Edit my countif formula to only use crieria if cells contains a value?

[tonywatsonhelp]

Hi Everyone,

I have this countif formula that works great, but I need it edited to do one other thing.

Currently it counts on 4 criteria like this.

"=COUNTIFS('Raw Data1'!AN:AN,Output!A5,'Raw Data1'!R:R,Output!H3,'Raw Data1'!P:P,Output!I3,'Raw Data1'!Q:Q,Output!J3)"

Now Output!H3,Output!I3,,Output!J3
are all drop down boxes, so I choose the data to put in, but sometimes I want to include all the results for that row, for example:

Output!H3, might = "Sales"
Output!I3, "Winter"
But I don't want to filter by "Output!J3" so the cell is blank. the problem is with the above formula instead of including all data, it sees blank as criteria and trys to find blank cells.

how can I get it that if there is nothing in the drop down cell it means count everything in that row whist keeping any other criteria I want???

please help i'm totally stuck.

Thanks

Tony

 

[Special-K99]

This had me stuck too for a while, even Googling didnt produce anything.
Try this (it may not be the best way to do it).

=COUNTIFS('Raw Data1'!AN:AN,Output!A5,'Raw Data1'!R:R,IF(Output!H3="","*",Output!H3),'Raw Data1'!P:P,IF(Output!I3="","*",Output!I3),'Raw Data1'!Q:Q,IF(Output!J3="","*",Output!J3))

If a dropdown is blank count everything by using wild character * else count just the dropdown box value.

 

[tonywatsonhelp]

Hi Special K99,
yep that does the job perfectly, thanks for your idea and input.

Tony