Forum Discussion
COUNTIFS TRICKERY
Hi
I'm hoping some genius can enlighten me here as my brain is in danger of going into meltdown. So, I have an excel spreadsheet with groups in one column and text in range spanning 5 columns. I want to create a formula that will count all the non-blank text cells in the range for each Group. For context, it is a set of objectives per group for lots of different categories.
My thought process was that if COUNTIF can count non-blank cells using "<>" and COUNTIFS can count using multiple criteria, why doesn't this work:
=COUNTIFS([GROUP RANGE],"[GROUP NAME]",[TEXT RANGE],"<>")
To further clarify, the text range spans 5 columns. The text within some of the cells in this range varies from a few words to some quite lengthy ones and I fear that it's the length of text that may be the issue. I just want Excel to look at the text range, look at the group range, and if the group matches the formula, tell me how many cells contain text. I tried the above with a "*" instead of the "<>" but that didn't work either. Please tell me it can be done. 🙂
3 Replies
COUNTIFS requires that all range arguments have exactly the same size. But the group range is 1 column while the text range has 5 columns.
Use SUMPRODUCT instead:
=SUMPRODUCT((GroupRange=GroupName)*(TextRange<>""))
- Thanks Hans. Hmm, ok, so I've done this:
=SUMPRODUCT((E4:E50="GROUPNAME")*(J4:N50="<>"))
Value error or 0 result (should be 2). I've tried (J4:N50<>) and (J4:N50,"<>") but still an error. I'm missing something here. Can you help?
Many thanks. It's driving me mad and I know I'm so close! 🙂
