Countif over several worksheets

[Steve]

Is there a quick way to do a countif function over 20 seperate worksheets if
the range is the same on each sheet?

Thanks,
Steve

 

[Frank Kabel]

Hi
try the following:
1. Put the names of your several sheets in a range on your summary
sheet. e.g. in cells X1:X10 (note: all cells must contain a valid
sheetname)

2. Use the following formula
=SUMPRODUCT(COUNTIF(INDIRECT("'" & X1:X10 & "'!A1:A10"),"search_text"))

 

[Steve]

=SUMPRODUCT(COUNTIF(INDIRECT("'" & M1:M36 & "'c4"),"=1")). That's what I cam
up with. Whats wrong with it. Where "search text" was i entered =1. I want to
know how many people entered a 1 in cell c4.

Thanks Again,
Steve

 

[Frank Kabel]

Hi
one missing exclamation mark at least
=SUMPRODUCT(COUNTIF(INDIRECT("'" & M1:M36 & "'!c4"),"1"))

And M1:M36 must ALL contain valid sheet names (no blank cells are
allowed)

 

[Steve]

Works like a charm. I have one more question and then i promise I will leave
you alone...Is it possible to tweak this formula at all and get an average
over the same range? So, an average for the values in cell C4 over all 36
worksheets?

Steve

 

[Steve]

How does this look?

=AVERAGE(INDIRECT("'" & M1:M36 & "'!c6"))

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(SUMIF(INDIRECT("'" & M1:M36 &
"'!c4"),"<>0"))/SUMPRODUCT(COUNTIF(INDIRECT("'" & M1:M36 &
"'!c4"),"<>0"))