# CountIf formula help

#### Martmorg

##### New Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.

=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

Replies
5
Views
420
Replies
0
Views
147
Replies
2
Views
185
Replies
5
Views
173
Replies
4
Views
294

1,221,525
Messages
6,160,326
Members
451,637
Latest member
hvp2262

### 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.

### Which adblocker are you using?

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

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