sumproduct(countifs(indirect( with comparisons help

[OleMiss2010]

I'm trying to count the instances across a set of sheets in a defined name of one cell being > another cell. The first two criteria in my formula below work perfectly and are there to identify the correct rows. When I add the third criteria, however, I only get 0's in my solution, which should not be the case. Any ideas?

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

 

[rafaelspeixoto]

Sumproduct does not work with full columns or rows (like "A:A"). Change it to a defined range, like "A1:A1000" (and make sure you use the same range size in all of them).

 

[OleMiss2010]

I tried that. Also, I think it will work with full columns, because it returns the correct answer if I only use the first two criteria. The "0" only answer only shows up when I add the third criteria.

 

[rafaelspeixoto]

Why are you using COUNTIFS inside a sumproduct?? COUNTIFS already gives the count

 

[Domenic]

If I understood you correctly, you'd like to count across multiple sheets the number of times a row in Column B equals the text value "Current:", and where the corresponding row in Column D equals the text value "Proposed:", and where the number in the corresponding row in Column C is greater than the number in the corresponding row in Column F. Is this correct? If so, try the following solution. Note, however, that the solution is resource intensive. So if the range is large, you'll find the calculations to be too slow, and you'll need an alternate solution.

First, if the sheet names are listed in a vertical range of cells instead of a horizontal range of cells, you'll need to change the reference for the defined name 'SheetList'. So, for example, if the reference for 'SheetList' is '=Sheet1!$A$2:$A$10', you'll need to change it to '=TRANSPOSE(Sheet1!$A$2:$A$10)'. Then, assuming that Rows 2 to 50000 contain the data, try...

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&SheetList&"'!B2:B50000"),ROW(INDIRECT("2:50000"))-2,0,1))="Current:"),--(T(OFFSET(INDIRECT("'"&SheetList&"'!D2:D50000"),ROW(INDIRECT("2:50000"))-2,0,1))="Proposed:"),--(N(OFFSET(INDIRECT("'"&SheetList&"'!C2:C50000"),ROW(INDIRECT("2:50000"))-2,0,1))>N(OFFSET(INDIRECT("'"&SheetList&"'!F2:F50000"),ROW(INDIRECT("2:50000"))-2,0,1))))

 

[OleMiss2010]

Domenic, you understand me perfectly.

My SheetList name is a verticle set. Where do I put that transpose into the equation?

 

[OleMiss2010]

rafaelspeixoto, the countifs inside a sumproduct allows me to utilize the indirect function and count across different worksheets.

 

[Domenic]

Domenic, you understand me perfectly.

My SheetList name is a verticle set. Where do I put that transpose into the equation?

Formulas > Defined Names > Name Manager > select 'SheetList' > change the reference in the 'Refers to' box

 

[OleMiss2010]

Okay I see what you mean now. The list is actually produced in a VBA and is referenced in other cells. No problem though. I can easily create a second list.