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

  • Thread starter Thread starter Dianna_P
  • Start date Start date
D

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!
 
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
 
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
 
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
 
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))
 
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.
 
I tried and it didn't work. I also tried an additional paranthesis after the
2nd and 3rd SUMPRODUCT.
 
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!
 
Back
Top