How do I count the number of cells that meet multiple criteria?

[Dianna_P]

I want to count cells from 3 worksheets that meet multiple criteria.

I've tried with a result of 0 (it should be 3):

=SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258=Mark!Y9)*(Mark!F9:F258=Mark!Y10)*(Craig!X9:X258=Info!B15)*(Craig!F9:F258=Craig!Y9)*(Craig!F9:F258=Craig!Y10)*(Dan!X9:X258=Info!B15)*(Dan!F9:F258=Dan!Y9)*(Dan!F9:F258=Dan!Y10)

I've also tried with a result of 0:

=SUMPRODUCT(--(Mark!X9:X258=Info!B15),--(Mark!F9:F258=Mark!Y9),--(Mark!F9:F258=Mark!Y10),--(Craig!X9:X258=Info!B15),--(Craig!F9:F258=Craig!Y9),--(Craig!F9:F258=Craig!Y10),--(Dan!X9:X258=Info!B15),--(Dan!F9:F258=Dan!Y9),--(Dan!F9:F258=Dan!Y10)

Please help!

 

[Shane Devenshire]

Hi,

First you are missing a parenthesis at the end. Second this would only
return a value if All conditions were true on the same row at the same time,
is that what you are checking?

So for example if Mark!X9:X258=Info!B15 is true for row 9 but
Mark!F9:F258=Mark!Y9 is true for row 10 then 0 will be your answer.

If this helps, please click the Yes button

Cheers,
Shane DEvenshire

 

[Bernard Liengme]

The syntax looks find, so the problem must be with the data.
I would test each part separately to find where the error is.
for example does this =SUMPRODUCT((Mark!X9:X258=Info!B15)*1) give you the
correct answer?
Next does this
=SUMPRODUCT((Mark!F9:F258=Mark!Y9)*1)
and this
=SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258=Mark!Y9))
Are you working with number, dates or text?
and so on

 

[Shane Devenshire]

Hi,

One other thing you might consider - if the entries are numeric or dates are
some of them text and other numeric? The data types must be the same in your
formula.

Cheers,
Shane Devenshire

 

[Bob Phillips]

Are you sure that it shouldn't be separate functions

=SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258=Mark!Y9)*(Mark!F9:F258=Mark!Y10))
+SUMPRODUCT(Craig!X9:X258=Info!B15)*(Craig!F9:F258=Craig!Y9)*(Craig!F9:F258=Craig!Y10))
+SUMPRODUCT(Dan!X9:X258=Info!B15)*(Dan!F9:F258=Dan!Y9)*(Dan!F9:F258=Dan!Y10))

 

[Dianna_P]

I am working with text.
I tried each part separately and I got correct answers.
Then I started combining parts and I got correct answers:

=SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258=Mark!Y9))
and =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258=Mark!Y10))

However, when I combine everything, I get 0 and it should be 3:

=SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258=Mark!Y9)*(Mark!F9:F258=Mark!Y10))

I guess I could just add the separate sections and get the same result.
Add the cell with the formula
=SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258=Mark!Y9)) to the cell with
formula =SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258=Mark!Y10)).

I just thought it could be combined.

 

[Dianna_P]

I tried and it didn't work. I also tried an additional paranthesis after the
2nd and 3rd SUMPRODUCT.

 

[Dianna_P]

Thought further and came up with the following with the correct answer:

=SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258=Mark!Y9))+SUMPRODUCT((Mark!X9:X258=Info!B15)*(Mark!F9:F258=Mark!Y10))

Thank you for all the help!