Countifs Function with Two Coulmns
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?? 😃
Best Answers

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 <> "")

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.
Answers

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 <> "")

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}?

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.

Thank you! That worked perfectly.

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!