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"))

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"))
--
HTH,
Barb Reinhardt



"JimG" wrote:

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"))

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.

"Barb Reinhardt" wrote:

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"))
--
HTH,
Barb Reinhardt



"JimG" wrote:

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"))

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.

--
Biff
Microsoft Excel MVP


"JimG" wrote in message
...
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.

"Barb Reinhardt" wrote:

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"))
--
HTH,
Barb Reinhardt



"JimG" wrote:

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"))

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"JimG" wrote in message
...
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.

"Barb Reinhardt" wrote:

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"))
--
HTH,
Barb Reinhardt



"JimG" wrote:

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"))

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"JimG" wrote in message
...
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.

"Barb Reinhardt" wrote:

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"))
--
HTH,
Barb Reinhardt



"JimG" wrote:

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"))