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 ...
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! 🙂
=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! 🙂