Countifs Function with Two Coulmns

TKomo

I am trying to create a function where it will count the number with specified criteria in one column as well as if 1 of 2 columns is not blank. I have the formula figured out where it will count 1 of those columns but cannot figure out how to add the "OR" piece so it will count data from column 2 only if column 1 is blank.

This is the existing formula: =COUNTIFS({Column 1}, <>"", {Location}, "location name")

The formula I am trying to create is something like: =COUNTIFS({Column 1}, <>"", OR {Column 2}, <>"", {Location}, "location name")

Any suggestions?? 😃

Paul Newcome

You will need to count both columns separately, add them together, then subtract the count of columns where both are not blank.

=COUNTIFS({Column 1}, @cell <> "") + COUNTIFS({Column 2}, @cell <> "") - COUNTIFS({Column 1}, @cell <> "", {Column 2}, @cell <> "")

Paul Newcome

You would add that range/criteria set into each of the COUNTIFS. I just threw that together as a quick example of how to add/subtract appropriately.

Paul Newcome

You will need to count both columns separately, add them together, then subtract the count of columns where both are not blank.

=COUNTIFS({Column 1}, @cell <> "") + COUNTIFS({Column 2}, @cell <> "") - COUNTIFS({Column 1}, @cell <> "", {Column 2}, @cell <> "")

TKomo

Thank you, that worked for all my data combined.

But now how can I make it so it only counts those that are at {location 1} and not {location 2} or {location 3}?

Paul Newcome

You would add that range/criteria set into each of the COUNTIFS. I just threw that together as a quick example of how to add/subtract appropriately.

TKomo

Thank you! That worked perfectly.

Paul Newcome

Happy to help. 👍️