Sumproduct to count

[Guest]

I'm trying to make a worksheet that keeps a running tally
of the number of times a specific value occurs in several
other worksheets.

I put the names of the seven worksheets that I want to
check in column A on worksheet entitled summary sheet.

The values to be checked to see if they match a value on
current sheet then counted if they do match are on
Column B of all seven worksheets to be checked.

The value to be matched from current sheet and counted in
other 7 worksheets is in column B

here's my formula
=SUMPRODUCT(COUNTIF(INDIRECT("'" & 'Summary sheet'!
$A$1:$A$7 & "'!B1:B100"),A1))

Unfortunately I keep getting a #ref error.

Any ideas?

thanks in advance

 

[Peo Sjoblom]

=SUMPRODUCT(COUNTIF(INDIRECT("'"&'Summary Sheet'!$A$1:$A$7&"'!B1:B100"),A1))

works for me

you can only get a ref error from that if one of the names in A1:A7 does not
match one of the sheet names,
could be as simple as an extra space in the beginning or at the end in
either one of the sheet tans or one of the sheet names in the
A1:A7 list

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Harlan Grove]

=SUMPRODUCT(COUNTIF(INDIRECT("'"&'Summary Sheet'!$A$1:$A$7&"'!B1:B100"),A1))

works for me

....

If these worksheets were in a single group, beginning with 'foo' and ending
with 'bar', then this could be done with a 3D reference.

=INDEX(FREQUENCY(foo:bar!$B$1:$B$100,A8*{0.99999999999999;1}),2)

 

[Peo Sjoblom]

Brilliant, that's one for the keepers. I am not sure though the OP meant
numeric values.

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)