SOLVED

New Contributor

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

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

I have succesffuly created a COUNTIF formula for the entire month '=COUNTIF(A202:AE202,"*")'.

However, I also want to identify weekend working. The COUNTIFS formula is based on the COUNTIF formula but its not picking up cells with text in them - I know some weekend cells in row 202 contain text. I was wondering if anyone could see any issue with the COUNTIFS formula which would cause this to happen.

Thanks

Roger

5 Replies

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

Perhaps you aren't aware of what your current formula is calculating? Just to clarify, that formula will only ever give a result of 0, 1 or 2, as follows:

2 - if all entries in F202:G202, M202:N202, T202:U202, AA202:AB202 contain text

1 - if either all entries in F202, M202, T202, AA202 contain text or all entries in G202, N202, U202, AB202 contain text

0 - otherwise

Regards

best response confirmed by Hans Vogelaar (MVP)
Solution

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

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

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

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

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

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

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

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.