Apr 14 2024 08:26 AM
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?
Apr 14 2024 09:09 AM
@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]) * ...
Apr 15 2024 12:40 PM
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.