Countif using tab range
 

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

Countif using tab range
 

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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DEI" wrote:

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

Countif using tab range
 

Luke M wrote...
....
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)

Countif using tab range
 

Doh! Thanks for finding that mistake Harlan.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Harlan Grove" wrote:

Luke M wrote...
....
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)