Count of leader on multiple sheets
 

I have a summary sheet with leader in Column B, and want count in
Column C of the number of times that the leader appears in the monthly
sheets. (Each month has it's own Sheet).

Cannot get the formula to work. Keep getting #VALUE! Can you
help?

=SUMPRODUCT(--('12-2007'!$C3:$C532=B35),--('11-2007'!$C3:$C532=B35),--
('10-2007'!$C3:$C532=B35),--('9-2007'!$C3:$C532=B35),--('8-2007'!
$C3:$C532=B35),--('7-2007'!$C3:$C532=B35),--('6-2007'!$C3:$C532=B35),--
('5-2007'!$C3:$C532=B35),--('4-2007'!$C3:$C532=B35),--('3-2007'!
$C3:$C532=B35),--('2-2007'!$C3:$C532=B35),--('1-2007'!$C3:$C532=B35),--
('12-2006'!$C3:$C532=B35),--('11-2006'!$C3:$C532=B35),--('10-2006'!
$C3:$C532=B35),--('9-2006'!$C3:$C532=B35))

Count of leader on multiple sheets
 

Put the names in M1:Mn (I have used M1:M5 in my example, and then use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M5&"'!C3:C532" ),B35))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jimbo" wrote in message
oups.com...
I have a summary sheet with leader in Column B, and want count in
Column C of the number of times that the leader appears in the monthly
sheets. (Each month has it's own Sheet).

Cannot get the formula to work. Keep getting #VALUE! Can you
help?

=SUMPRODUCT(--('12-2007'!$C3:$C532=B35),--('11-2007'!$C3:$C532=B35),--
('10-2007'!$C3:$C532=B35),--('9-2007'!$C3:$C532=B35),--('8-2007'!
$C3:$C532=B35),--('7-2007'!$C3:$C532=B35),--('6-2007'!$C3:$C532=B35),--
('5-2007'!$C3:$C532=B35),--('4-2007'!$C3:$C532=B35),--('3-2007'!
$C3:$C532=B35),--('2-2007'!$C3:$C532=B35),--('1-2007'!$C3:$C532=B35),--
('12-2006'!$C3:$C532=B35),--('11-2006'!$C3:$C532=B35),--('10-2006'!
$C3:$C532=B35),--('9-2006'!$C3:$C532=B35))