Forum Discussion

Roger_Bullen's avatar
Roger_Bullen
Copper Contributor
Feb 26, 2023
Solved

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's avatar
    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's avatar
JosWoolley
Iron Contributor
Feb 26, 2023

Roger_Bullen 

 

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

  • Roger_Bullen's avatar
    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's avatar
      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. 

Resources