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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top