Countif on multiple sheets

[daniels012]

Is is possible to use countif on multiple pages?

I need to count a guys name on
Sheet1!J10:J52 and Sheet2!J10:J52 and Sheet3!J10:J52

Thanks,
Michael

 

[jeffreybrown]

Hi Michael,

Take a look here...

http://www.mrexcel.com/forum/showthread.php?t=530419

 

[barry houdini]

If your sheets are actually "Sheet1", "Sheet2" etc. you can do that like this

=SUM(COUNTIF(INDIRECT("Sheet"&{1,2,3}&"!J10:J52"),Z2))

where your name to count is in Z2

 

[daniels012]

Thank You both!
barry,
what if my sheets have names?
jeffery,
I see yours uses sheet names, I guess?
I tried yours and this doesn't work?

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Divisions&"'!J10:J52"),">0"))

I have in cells I2:I4 the Sheet names. I created a name for them called "Divisions".

Any thoughts on why it is not working? No error, just a 0 as a result.
Should I just use a countif from each sheet, since it is only 3 sheets?

Michael

 

[jeffreybrown]

Are you counting a specific name?

If so

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Divisions&"'!J10:J52"),"Sally"))

 

[daniels012]

Nice!

Worked perfectly!
Thank you,
Michael

 

[jeffreybrown]

You're welcome...glad you got it working