I’m not the most well-versed Calc user but most of the time I can usually find terribly inefficient ways to reach my desired end but I am at a loss this time.
I have a COUNTIFS formula that I use to filter through lots of data, my issue comes up when I try to introduce a variable drop down box. One of the COUNTIFS “criteria” is trying to match this variable drop-down box, I belive the issue stems from leaving the drop down blank. To be clear I very much want this as an option. I want the blank cell to NOT filter the data down at all, but instead the formula looks for blank cells (obviously) and filters my data in a way I do not want.
Is there a way I can have a ‘range’ as part of COUNTIFS that can be ignored if the ‘criteria’ is blank?
Here is the formula I’m trying to use (unsuccessfully) - it returns err 502
=COUNTIFS($data.$D:$D,"=W",$data.$M:$M,"=John",$data.$N:$N,J8,$data.$AD:$AD,IF(ISBLANK(J1),"*",J1))
So it should count if Column D is a “W”, Column M is “John”, Column N is J8, … all this works just fine, i believe the error comes next… then I want it to count if column AD matches cell J1. Ideally if J1 is blank it would omit this last filter criteria for the ‘count’, but I’m not sure thats possible. So I used the IF(ISBLANK to try and force a wildcard filter on a Blank J1, but it’s not doing what I want it do.
Any help here is greatly appreciated. I can elaborate further and apologize if my explanation is poor.