COUNTIFS ignore blank cells

[Kirk_Anderson]

Hey all, I've got a Workbook that has a table on each Worksheet organised by year. I've made a little "query box" on the front that is basically a table with a dropdown under each header cell and a cell at the end that adds up what is selected in the preceding cells. The formula is:

=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])

It works really well, except I can't get it to ignore dropdowns that have not been selected. So if an option has not been selected in the Gender column (i.e it's a blank cell), I want it to ignore that and just do the other bits, which would result in all Gender options being included. Any ideas?

[#2]

@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]) * ...

[#3]

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.