Countif over multiple sheets

davemorse

Board Regular
Joined
Mar 15, 2006
Messages
141
Hi all,
Im trying to to do a Countif over a number of sheets but for some reason my formula returns #value. Im trying to count the number of "Y"'s in cell B2 over each sheet.

Ive highlighted cell B2 and selected my 7 sheets from August 06 - Jan 07..and typed this is my formula

=COUNTIF('August 06:Jan 07'!B2, "y")

Am i doing something wrong?

Thanks
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Why not do a countif formula for each sheet (on a seperate results worksheet), then cumulate these figures together?

EG

=COUNTIF(Sheet1!G:G,"no")
=COUNTIF(Sheet2!G:G,"no")
=COUNTIF(Sheet3!G:G,"no")
=COUNTIF(Sheet4!G:G,"no")
=COUNTIF(Sheet5!G:G,"no")
=COUNTIF(Sheet6!G:G,"no")

You could use

=COUNTIF(G:G,"no")+COUNTIF(Feb!G:G,"No") etc
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Hi all,
Im trying to to do a Countif over a number of sheets but for some reason my formula returns #value. Im trying to count the number of "Y"'s in cell B2 over each sheet.

Ive highlighted cell B2 and selected my 7 sheets from August 06 - Jan 07..and typed this is my formula

=COUNTIF('August 06:Jan 07'!B2, "y")

Am i doing something wrong?

Thanks

Do you mean B:B on each sheet? or is there 'YYY' in B2 on each sheet? (do you want to count the number of times 'Y' appears in B2?)
 

davemorse

Board Regular
Joined
Mar 15, 2006
Messages
141
Hi,
I want to see if there is a "Y" in any of the sheets in cell B2 and then count up how many "Y"'s there are in all of the sheets.
 

Gohar Shahab

Active Member
Joined
May 17, 2006
Messages
339
You can use formula like,,

=countif(range(August 06 sheet),"y")+countif(Range(Jan 07 sheet),"y")

As below for range you may have different ranges...

=COUNTIF('August 06'!A1:A8,"y")+COUNTIF('Jan 06'!A1:A8,"y")

Gohar
Using XL 2003
 

Forum statistics

Threads
1,170,942
Messages
5,872,859
Members
432,950
Latest member
ALeXceLBr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top