COUNT formula that will count one or both conditions.
There are 2 columns that lists the name in one of the columns or both. When the name pops up in one column, I want that to be counted as one. When it pops up in one of the columns, I also want that to be counted as one. Is there a formula for this?
Best Answer

Try this...
=COUNTIFS([First Column]:[First Column], @cell = "Name") + COUNTIFS([First Column]:[First Column], @cell <> "Name", [Second Column]:[Second Column], @cell = "Name")
Answers

Are you able to provide a screenshot with some sample data manually entered?

I'd like a COUNT formula that counts when there are 2 kacys or at least one kacys in each row

All in all, I'm looking for a formula that will count cells that contain x or y or both. Thank you for your help!

Try this...
=COUNTIFS([First Column]:[First Column], @cell = "Name") + COUNTIFS([First Column]:[First Column], @cell <> "Name", [Second Column]:[Second Column], @cell = "Name")

Thank you so much @Paul Newcome !! Also, I'm trying to count the below but not include the ones with "" How do I include that in the COUNTIFS formula?

=COUNTIFS([First Column]:[First Column], @cell = "Name") + COUNTIFS([First Column]:[First Column], @cell <> "Name", [Second Column]:[Second Column], @cell = "Name")
How do I include not including blanks as well for this portion of the formula  COUNTIFS([First Column]:[First Column], @cell <> "Name"?

This should take care of excluding both blanks and those with a "" in them:
COUNTIFS([First Column]:[First Column], AND(@cell <> "Name", @cell <> "", FIND("", @cell) = 0))

Thank you! This part of the formula @cell <> "", FIND("", @cell) = 0 doesn't seem to work on my formula. It's still counting the project numbers (Proj. No.) with "" in it.
This is my formula. Do you have any recommendations?
=COUNTIFS({Phase}, PLANNING$1, {Proj. No.}, FIND("", @cell) = 0, {LCS}, $[LCS/LCO]2) + COUNTIFS({Phase}, PLANNING$1, {Proj. No.}, FIND("", @cell) = 0, {LCS}, AND(@cell <> $[LCS/LCO]2, @cell <> ""), {LCO}, $[LCS/LCO]2)

I would suggest breaking the COUNTIFS into two separate formulas to make sure they are both calculating correctly individually.

=COUNTIF({PRJ}, FIND("", @cell) = 0)
The total number of projects are 61, but I am trying to exclude 2 which have "" in it, but it's populating 69. Any tips? Thank you so much!

Try this to also exclude blanks:
=COUNTIFS({PRJ}, AND(FIND("", @cell) = 0, @cell <> ""))

So the exclusion of blanks was for a different column. I am just trying to exclude "" in the project number column.

Right. But when you count all rows that do not have a "" in them, blank rows also do not have a "" in them, so it may be picking those blank rows up.

Oh, I see. Thank you so much @Paul Newcome !! And sorry for the troubles

Help Article Resources
Categories
Check out the Formula Handbook template!