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

Roger_Bullen

Copper Contributor

Feb 26, 2023

Thanks JosWoolley . I was expecting a count of all instances similar to the result I get when using COUNTIF.

That being the case, should I be using another type of formula? What formula is suitable if the range is not continuous?

I just tried '=COUNTIF(INDIRECT({"F3:G3","M3:N3","T3:U3","AA3:AB3"}),"*")' but this doesn't pick up any results either.

Roger

JosWoolley

Iron Contributor

Feb 26, 2023

Roger_Bullen

True, you could also use

=SUM(
    COUNTIF(
        INDIRECT({"F202:G202","M202:N202","T202:U202","AA202:AB202"}),
        "*"
    )
)

though it's volatile and inflexible, so I'd not recommend it.

Forum Discussion

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

Resources