MS Excel - COUNTIFS not picking up instances of text in cells

Hello, I've set up this COUNTIFS formula to pick up text references in cells in a monthly timesheet so that I can calculate if work activity has taken place during specific hours of the day. ...

Formulas and Functions

JosWoolley
Feb 26, 2023
Roger_Bullen

I believe what you intended was:
=SUM( COUNTIFS(F202:G202, "*"), COUNTIFS(M202:N202, "*"), COUNTIFS(T202:U202, "*"), COUNTIFS(AA202:AB202, "*") )
though this can't easily be abbreviated using COUNTIFS. It might be worth switching to COUNTA:
=COUNTA( F202:G202, M202:N202, T202:U202, AA202:AB202 )
though this will count numerics as well, if that's an issue.

Regards

JosWoolley

Iron Contributor

Feb 26, 2023

Roger_Bullen

I believe what you intended was:

=SUM(
    COUNTIFS(F202:G202, "*"),
    COUNTIFS(M202:N202, "*"),
    COUNTIFS(T202:U202, "*"),
    COUNTIFS(AA202:AB202, "*")
)

though this can't easily be abbreviated using COUNTIFS. It might be worth switching to COUNTA:

=COUNTA(
    F202:G202,
    M202:N202,
    T202:U202,
    AA202:AB202
)

though this will count numerics as well, if that's an issue.

Regards

Roger_Bullen

Copper Contributor

Feb 26, 2023

JosWoolley I didn't see your suggestion before I replied. I've tried the COUNTA option and that works very well. Thanks very much!

Forum Discussion

MS Excel - COUNTIFS not picking up instances of text in cells

Resources