CountIf formula help

[Martmorg]

Good Afternoon,

I am trying to calculate how many 'A12' cells are completed with 'Yes' across multiple tabs and am using the below forumula which does not appear to work. Anyone have any suggestions what is going wrong with the formula i am using or an alternative?

=Countif('Tab Name 1:Tab Name 20!A12,"Yes")

Any help greatly appreciated

Regards

Martin

 

[Oeldere]

See in the link below, #6 why your solution will not work for you.

Count text across many sheets (Not VBA)

 

[XOR LX]

Hi.

Unfortunately, COUNTIF is not capable of operating on 3D ranges as such.

You would need to do it as follows:

First enter all of your sheet names (precisely as they appear on the tabs) into a vertical range somewhere in your master sheet, e.g. H1:H20.

A quick way to do this (assuming your sheet names follow the pattern "Tab Name " followed by the integers from 1 to 20) would be to enter, in H1:

="Tab Name "&ROWS($1:1)

and copy down to H20.

Your formula would then be:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H20&"'!A12"),"Yes"))

If you don't like the idea of having your sheet names in the actual worksheet, there is an alternative in which we could store them directly into Name Manager. Let me know if that would be preferable to you.

However, arguably even better than all this is to simply use an extra cell in each of your 20 worksheets, e.g. A13, with the formula:

=0+(A12="Yes")

Then in your master sheet, simply:

=SUM('Tab Name 1:Tab Name 20'!A13)

Regards