Count across multiple worksheets

[CatRoberts]

Hi there,

I've created a workbook and need to count the number of cells containing letters across the same row in the 12 worksheets.

My formula so far is:

=COUNTIFS(October!B5:AF5,"=V",November!B5:AF5,"=V")

I need to include all the months of the year (12 worksheets)

The formula will calculate correctly if I have only one month, i.e one worksheet, but when I add more it stops counting?

I'm very much a novice at excel, but what am I doing wrong? I thought i'd cracked it

Many thanks for your help

Cat

 

[Cross Terrier]

You need to use a COUNTIF for each worksheet instead of trying to squeeze every sheet into the same formula.

=COUNTIF(October!B5:AF5,"=V")+COUNTIF(November!B5:AF5,"=V")+etc....

 

[Aladin Akyurek]

Create a range housing the names of the relevant sheets. Name that range SheetList via the Name Box.

Now invoke:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!B5:AF5"),"=V"))

 

[CatRoberts]

Thank you so much! It worked like a charm

 

[Aladin Akyurek]

Thank you so much! It worked like a charm

You are welcome.

 

[Ganesan934]

Hi, If i add one more criteria in below code, i got an error

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!B5:AF5"),"=V"))*AND SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!B5:AF5"),"=V"))

 

[Aladin Akyurek]

Hi, If i add one more criteria in below code, i got an error

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!B5:AF5"),"=V"))*AND SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!B5:AF5"),"=V"))

You can’t have AND the way you did. By the way, what is your second condition?

 

[Ganesan934]

You can’t have AND the way you did. By the way, what is your second condition?

Sorry there was a mistake in my post:

Here is the correct one:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!B5:B55"),"=Vital Part"))*AND(SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!B5:B55"),"=A")))

My condition is to count if Vital Part & A both matched,

If i use the above one it will count if " Vital Part & B " comes.

Thanks for the timely reply Aladin.
Thanks a lot.

 

[Aladin Akyurek]

Does the following work?


=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!B5:B55"),{"=Vital Part","=A"})))

 

[Ganesan934]

No... it doesn't work. It counts both vital part ,A and gives the count as 2.