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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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,136,510
Messages
5,676,281
Members
419,617
Latest member
Shane50GT

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