Countif using tab range

[DEI]

This seems intuitive to me, but I keep getting an error. I would like to
apply a countif statement to a column in a range of tabs within a workbook,
i.e. =COUNTIF('TAB1:TAB5'!F:F,A2). Is there anyway to do this without
writing a function for each individual column?

Thanks in advance.

DEI

 

[Luke M]

i'll assume the A2 you refer to is on sheet "Summary"

Unfortunately, COUNTIF is not a 3d function. You can do it with a helper
cell though.
Find a cell that is blank on all your sheets (Say, Z1). Now, select all
your sheets you want to apply the countif in, and then type in Z1
=COUNTIF('TAB1:TAB5'!F:F,'Summary'!A2)
You can this hide this cell (again, having all sheets selected).

Now, in the cell you originally wanted the COUNTIF formula in
=SUM('TAB1:TAB5'!Z1)

 

[Harlan Grove]

Unfortunately, COUNTIF is not a 3d function. You can do it with a helper
cell though. ....
=COUNTIF('TAB1:TAB5'!F:F,'Summary'!A2)

....

This is a typo. As you correctly point out, COUNTIF doesn't support 3D
references, so the formula above will return #VALUE! errors. You'd
need to use

=COUNTIF(F:F,'Summary'!A2)

 

[Luke M]

Doh! Thanks for finding that mistake Harlan.