or Explore Discussions

# COUNT formula that will count one or both conditions.

04/26/21
Accepted

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?

• Try this...

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

• 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 [email protected] <> "", 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

• No worries. Happy to help. 👍️