Forum Discussion
COUNTIFS ignore blank cells
Kirk_Anderson one option is to simply add &"*" to each. This assumes that none of the values will be redundant then. For example preceeding each with "*"& might be an issue if you use "male" and "female" as when you enter "male" it would be "*male" which means both "male" and "female" are included. By adding the "*" after this wouldn't be a problem in THIS case but you would have to make sure it won't be a problem in any other case
so:
=COUNTIFS(Apr22Apr23[Location],[@Team]&"*",Apr22Apr23[Gender],[@Gender]&"*",Apr22Apr23[Referred For],[@Assessment]&"*")+
COUNTIFS(Apr23Apr24[Location],[@Team]&"*",Apr23Apr24[Gender],[@Gender]&"*",Apr23Apr24[Referred For],[@Assessment]&"*")+
COUNTIFS(Apr24Apr25[Location],[@Team]&"*",Apr24Apr25[Gender],[@Gender]&"*",Apr24Apr25[Referred For],[@Assessment]&"*")if this isn't a possibility you may have to switch to something like
=SUMPRODUCT( (IF([@Team]<>"",[@Team],1)=Apr22Apr23[Location]) * ...
- Kirk_AndersonApr 15, 2024Copper Contributor
Thanks m_tarler, I've made the changes and it seems to work as long as I don't add more than four options in (original plan was seven I think). I'm just trying the SUMPRODUCT option, but I'm getting 0 back.