COUNTIF

  • Thread starter Thread starter Kevin B
  • Start date Start date
K

Kevin B

I need to use the COUNTIF across several worksheets. The
formula for one sheet is a breeze, but I cannot figure out
how to get it to look across multiple sheets. Any ideas?
 
Hi Kevin,

COUNTIF can't be used accross multiple sheets directly.
Have a look at SUMPRODUCT. If you need additional help,
post back with more detail.

Biff
 
OK, if then SUMPRODUCT is the route to go, I need to
establish the Array principle?

Here is what I have.

Worksheets labeled A-Z. On each sheet is a list pf persons
I need to keep track of by gender. "B" for Boy and "G" for
girl. I want to have a Stats sheet that displays the end
result of how many there are of each gender.

I am still trying to understand the Array idea.

Kevin
 
Hi Kevin,

Here are some options:

Because the sheet names are A:Z that makes this more
complex and the formula is *much* longer:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&
{"A","B","C","D","E","F","G","H"}&"'!A1:A5"),"B"))

You would list all the sheet names through Z. I stopped at
H, but I think you get the idea.

Another option, and a much shorter formula, if the default
sheet names were used - eg: Sheet1:

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:26"))
&"'!A1:A5"),"B"))

And the simpliest option: Do a COUNTIF on each sheet and
put that formula in the same cell in each sheet. Then
simply SUM those cells:

=COUNTIF(A1:A5,"B")

=SUM(A:Z!A6)

Biff
 
I took the third route. It was funny, after a good night's
sleep, it came to me to just add COUNTIF to each sheet,
then copy/paste this to the stat sheet and let it count
them there for the total. Took a little longer to do, but
came to the same result.

Thanks for all your help, it got the brain working again.

Kevin
 
Biff said:
Hi Kevin,

Here are some options:

Because the sheet names are A:Z that makes this more
complex and the formula is *much* longer:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&
{"A","B","C","D","E","F","G","H"}&"'!A1:A5"),"B"))
Click to expand...
Or, in this special situation:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&
CHAR(64+ROW(INDIRECT("1:26")))&"'!A1:A5");"B"))
 
Back
Top