SumProduct CountIF issue

[JimG]

I am trying to count the number of times the value "F" appears in column G,
if the value "HCSO" appears on the same row in column J. And I need it to
calculate across 31 worksheets. I tried this but it returns the total number
of times "F" appears. Any Ideas?

=SUMPRODUCT(SUMIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!J:J"),"=HCSO"))--SUMPRODUCT(COUNTIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!G:G"),"=F"))

 

[Barb Reinhardt]

This *May* be missing a comma. Untested.

=SUMPRODUCT(SUMIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!J:J"),"=HCSO")),--SUMPRODUCT(COUNTIF(INDIRECT("'7-"&ROW(INDIRECT("1:31"))&"'!G:G"),"=F"))

 

[JimG]

Thanks for the assist, but the formula is still calculating all of the values
of "F" instead of of only when the value of "HCSO" is in the corresponding
row. I just cannot figure out why.

 

[T. Valko]

There is no **easy** way to do this with a *single* formula using the
built-in functions.

Your *best* option is to put a formula on each sheet in the same cell then
sum that cell on your summary sheet.

There is a free add-in that has a function that can do this easily *but*
this add-in won't work with the format of your sheet names. A work-around to
this is to create a sheet "sandwich" where you put empty sheets on both
sides of the sheets you want to include in the calculation. Sort of like
this:

| start | 7-1 | 7-2 | 7-3 | end |

Then you use a formula that calculates every sheet between start:end
(inclusive).

Here's a link to the add-in:

http://xcell05.free.fr/morefunc/english/index.htm

And this would be the formula:

=SUMPRODUCT(--(THREED(start:end!G1:G10)="F"),--(THREED(start:end!J1:J10)="HCSO"))

Note that the total size of the reference (all cells in all sheets) can't be
more than 65536 items. So, this means that you can't use entire columns as
range references.

 

[JimG]

This is the second time in just one week you have helped me out. Thanks!!

 

[T. Valko]

You're welcome. Thanks for the feedback!