# Counting Cells With Multiple Criteria on Multiple Sheets

E

#### Eric Meiers

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.

Some background should help:
-- \$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.

Any help is deeply appreciated.
Eric