Counting Cells With Multiple Criteria on Multiple Sheets

**ericmeiers** · 04-20-2012, 10:33 AM

I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell
is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$39&"'!$R1"),2*(AND("'"&$H$1:$H$39&"'!$E1">"'"&$H$1:$H$39&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$39&"'!$S1"),2*(AND("'"&$H$1:$H$39&"'!$G1">"'"&$H$1:$H$39&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$39&"'!$T1"),2*(AND("'"&$H$1:$H$39&"'!$I1">"'"&$H$1:$H$39&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$39&"'!$U1"),2*(AND("'"&$H$1:$H$39&"'!$K1">"'"&$H$1:$H$39&"'!$L1"))))

but it returns a value of zero each time. Clearly there is an error in the formula.

Here is some background:
-- $H$1:$H$39 is a block of cells that has the names of the sheets in the spreadsheet
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing. In this case, I only want to count (add 1) when R1=2 and E1>F1 or when S1=2 and G1>H1 or when T1=2 and I1>J1 or when U1=2 and K1>L1.

Your help is greatly appreciated.
Eric

**JBeaucaire** · 04-21-2012, 01:26 PM

You must use INDIRECT() at every spot within your formula where you want to invoke your list of sheetnames. You didn't do it enough times. No way to test this formula for you, but I can correct your use o INDIRECT() like so:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$39&"'!$R1"), 2 * (AND(INDIRECT("'"&$H$1:$H$39&"'!$E1") > INDIRECT("'"&$H$1:$H$39&"' !$F1"))))) + SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$39&"'!$S1"), 2 * (AND(INDIRECT("'"&$H$1:$H$39&"'!$G1") > INDIRECT("'"&$H$1:$H$39&"'!$H1"))))) + SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$39&"'!$T1"), 2 * (AND(INDIRECT("'"&$H$1:$H$39&"'!$I1") > INDIRECT("'"&$H$1:$H$39&"'!$J1"))))) + SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$39&"'!$U1"), 2 * (AND(INDIRECT("'"&$H$1:$H$39&"'!$K1") > INDIRECT("'"&$H$1:$H$39&"'!$L1")))))

**ericmeiers** · 08-04-2012, 11:23 AM

Hi Jerry, thank you for the reply.
Unfortunately, the formula is not producing the correct answer.
For example, if I reduce the equation to just the necessary portion to get a non-zero result:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$4:$H$4&"'!$U1"),2*AND(INDIRECT("'"&$H$4:$H$4&"'!$K1")>INDIRECT("'"&$H$4:$H$4&"'!$L1")))))
the answer should be 1, not 0.
As you can see I am pointing the formula to just one sheet, $H$4, in the entire workbook (for the sake of speed).
When cell U1=2 and K1>L1 on sheet $H$4, I should get 1, since K1=8 and L1=1. Instead, the result is 0.
Are you able to provide any further help on this?
(Sorry for the delayed reply. I have been away/busy since.)
Thanks much,
Eric

**JBeaucaire** · 08-04-2012, 12:41 PM

1) it should be $H$4, not $H$4:$H$4.
2) Sometimes you can spot the problem visually by using the Evaluate Formula function from the Formula Ribbon
3) Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook if you want me to examine your formula(s) in action.

**ericmeiers** · 08-04-2012, 07:17 PM

Understand, but the cell set is not driving the error. Even when the formula is reduced to $H$4, the result is still the same---zero.
I've attached a test set so you can see exactly what I'm dealing with---even if it does just have one score sheet in the workbook.
I appreciate your kindness in helping me get this figured out.
Eric

**JBeaucaire** · 08-04-2012, 10:23 PM

A simpler version of your formula in C19 would be:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1&"'!$R1:$U1"), COLUMN(B1)))

...then just copy to the right.

Copy D19 down to D20, then change it to:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1&"'!$R2:$U2"), COLUMN(C2)))

...copy to right. Lather, rinse, repeat.

Looking at that basic formula, it IS counting how many times the value in R1:U1 = 1, or 2, etc.

====================

Adding back in the checking of the pairs of columns means going back to ugly monster formulas. In C19, then copied everywhere:

=--(OR(AND(INDIRECT("'" & $H$1 & "'!$E" & ROW($B1))>INDIRECT("'" & $H$1 & "'!$F" & ROW($B1)), INDIRECT("'" & $H$1 & "'!$R" & ROW($B1))=COLUMN(B$1)), AND(INDIRECT("'" & $H$1 & "'!$G" & ROW($B1))>INDIRECT("'" & $H$1 & "'!$H" & ROW($B1)), INDIRECT("'" & $H$1 & "'!$S" & ROW($B1))=COLUMN(B$1)),AND(INDIRECT("'" & $H$1 & "'!$I" & ROW($B1))>INDIRECT("'" & $H$1 & "'!$J" & ROW($B1)),INDIRECT("'" & $H$1 & "'!$T" & ROW($B1))=COLUMN(B$1)),AND(INDIRECT("'" & $H$1 & "'!$K" & ROW($B1))>INDIRECT("'" & $H$1 & "'!$L" & ROW($B1)),INDIRECT("'" & $H$1 & "'!$U" & ROW($B1))=COLUMN(B$1))))

This does give a few slightly different results, most are the same.