Using COUNTIF in multiple wooksheets

[Guest]

I am trying to use countif to count the word "complete" in a range on
mulitple wooksheets. I select my range and then hold shift to select other
worksheets to include but it comes up with #value. When I do it with one
worksheet it works fine. Help would be appreciated.

Thanks

Kevin

 

[Peo Sjoblom]

COUNTIF does not work with multiple sheets

First put a list with all sheets in a range on the summary sheet, assume you
have 10 sheets you can put ALL their sheet names in for instance H1:H10,
assume you want to count in the range A1:A10 in those sheets

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H2&"'!A1:A10"),"complete"))

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Guest]

sorry I am not sure what you mean?? you may have to explain a little more
simple because I am not to familiar with these calculations.

 

[Peo Sjoblom]

Don't know if I can explain further, what are your sheet names, for example
if your sheet names are
ASheet, BSheet, CSheet etc. you put each name in a cell t (in my example
H1:H10)

H1 ASheet
H2 BSheet
H3 CSheet
etc

meaning H1 holds A, H2 B, H3 C and so on, put the names of all sheets in a
list, change the A1:A10 in my formula to the range where you want to test
for "complete" in all sheets and replace H1:H10 with the range of the list
where you put the sheet names, then it is set to go
--
Regards,

Peo Sjoblom

(No private emails please)