Forum Discussion
Countifs returning all rows with 006, 007 in
Hey all!
I'm trying to return a count of all rows in column L that start with 006 and 007. My formula is as below
=Countifs(Sheet1!L:L,"006*",Sheet1L:L,"007*") My formula sits on a different sheet to the data.
This formula returns 0
=Countif(Sheet1!L:L,"006*") gives me the correct count for the 006's so what the holy heck am I doing wrong?
=Countifs(Sheet1!L:L,"006*",Sheet1L:L,"007*") counts the number of rows in column L whose value starts with 006 and with 007 at the same time. That is an impossible combination, so the result is 0.
You WANT
=COUNTIF(Sheet1!L:L,"006*")+COUNTIF(Sheet1L:L,"007*")
or
=SUMPRODUCT(COUNTIF(Sheet1!L:L,{"006*","007*"))
- Thank you so much. I'll tuck the second option in my pocket too 🙂
- COUNTIFS(...) will AND the conditions meaning both or all the conditional must be true. Since none of the rows can start with 006 AND 007 at the same time you get 0 try this instead:
=Countifs(Sheet1!L:L,"006*") +Countifs(Sheet1L:L,"007*")- Amazing! Thank you. It works and my blood pressure is slowly lowering!
