Sumproduct(Countifs(Indirect( Comparison question

[OleMiss2010]

I am attempting to count the number of times one cell is greater than another cell across N number of sheets. The row that contains the number can be repeated many times within different sheets but is easily identifiable. My current equation is:


=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!B:B"),"Current:",INDIRECT("'"&SheetList&"'!D:D"),"Proposed:",INDIRECT("'"&SheetList&"'!C:C"),">"&INDIRECT("'"&SheetList&"'!F:F")))


It works until I add the third criteria range.


Any ideas as to what I'm doing wrong?

 

[Luke M]

I don't think COUNTIFS and/or SUMPRODUCT can be used in 3D functions. Even then, the sheet callout is expecting something like "Sheet1:Sheet3". Giving it a range with the names of sheets shouldn't work. You probably need to have a COUNTIF function on each worksheet, and then use a 3D sum function.


If that's not viable, you could try writing a UDF. If you do go that way, I'd advise against having to search through every cell in the entire column.

 

[OleMiss2010]

The thing is the Sumproduct(Countifs works for the first two indirect ranges, just not when I add the part that compares two cells.

 

[Mike86]

When you say it doesn't work, does it not give the right answer or does it give an error?


I played with this for a bit. The closest thing I got to work was:


=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!A:A"),"Yes",INDIRECT("'"&SheetList&"'!B:B"),"Yes",INDIRECT("'"&SheetList&"'!C:C"),"="&F1))


Note that the third comparison here is to a single cell. The comparison used doesn't matter, as long as it is to a single cell. Every time I tried to generalize the third term, per your example, to compare to cells across sheets, it errored.


Otherwise, this is an interesting technique. The double and single quotes are a pain, but it does seem to work.

 

[OleMiss2010]

So here's a solution I got from Mr. Excel. I haven't tested yet but wanted to share http://www.mrexcel.com/forum/showthread.php?t=582592