# excel formula countif

carmella 15:29 25 Jun 03
Locked

I have an excel document with sheet 1,2 and 3.
In sheets 1 and 3 I enter values. Sheet 2 contains formulas which should calculate values based on what I've entered in sheets 1 and 3.

I have a CountIf fomula which works fine if I refer to sheet 1 only. I can't seem to work out how to enter a formula that will look for values in sheets 1 AND 3.

Here's the formula I have:
=COUNTIF('Jan-May'!D4:DG4,"H")
where Jan-May is sheet 1. Now I need to add Jun-Dec, which is sheet 3.

Any ideas?

many thanks
C

VoG® 15:35 25 Jun 03

COUNTIF can only test one condition. You need to use SUMPRODUCT

=SUMPRODUCT(('Jan-May'!D4:DG4="H") * ('Jun-Dec'!D4:DG4="H"))

carmella 15:50 25 Jun 03

I get a result False or True with this formula, whereas what I need is to add how many times the value "H" appears in sheets 1 and 3.

So if I entered "H" 3 times in sheet 1, and 4 times in sheet 3, the formula in sheet 2 should calculate 7.

thanks again
C

Megatyte 15:53 25 Jun 03

The * should be a +

AH

carmella 15:58 25 Jun 03

I've now tried this:
=SUMPRODUCT('Jan-May'!D6:DG6="H")*('June-Dec'!D6:FB6="H")

and this:

=SUMPRODUCT('Jan-May'!D6:DG6="H")+('June-Dec'!D6:FB6="H")

but get results "0", which is not right.

any help appreciated.

C

VoG® 16:02 25 Jun 03

I just did a mock-up using three sheets and on the third sheet used

=SUMPRODUCT((Sheet2!A1:A5="a")*(Sheet1!A1:A5="a"))

which returns the number of times "a" occurs in the same row of Sheets 1 and 2.

Megatyte 16:02 25 Jun 03

You are missing a set of parenthesis, After Sumproduct and at the end.

AH

VoG® 16:04 25 Jun 03

Megatyte is correct if you want to return the result 7.

carmella 16:26 25 Jun 03

I hate to insist on this, but it doesn't work!
To make things simpler, I also tried it on a new spreadsheet, with values a in sheets 1 and 2, then the exact formula suggested, with all required parenthesis, and get a #False result. Without one set of parenthesis, I get 0.

sorry if I'm missing something right under my nsse here, but I think I am.

C

VoG® 16:49 25 Jun 03

I've mailed you.

Megatyte 17:18 25 Jun 03

With the parenthesis as required and + instead of * you should get the right result. Using the * will return 0.

ah

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now