Countifs with variable

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


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.

Please attach a sample file.

Your formula should work, if your spreadsheet settings allow wildcards in formula, and if your J1 is really blank (not a space). Your formula should only return error 502, if one of the criteria cells is error 502.

Err.502 is caused by a mismatch of data types. For example when a function expects a date format but isn’t given one by the referenced cell. Double check your data formats.