COUNT formula that will count one or both conditions.

Hyo Jung Yoon - 10PM-C

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?

Paul Newcome

Try this...

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

Paul Newcome

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

Hyo Jung Yoon - 10PM-C

InkedCapture_LI.jpg

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

Hyo Jung Yoon - 10PM-C

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!

Paul Newcome

Try this...

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

Hyo Jung Yoon - 10PM-C

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?

Hyo Jung Yoon - 10PM-C

=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"?

Paul Newcome

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

Hyo Jung Yoon - 10PM-C

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)

Paul Newcome

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

Hyo Jung Yoon - 10PM-C

=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!

Paul Newcome

Try this to also exclude blanks:

=COUNTIFS({PRJ}, AND(FIND("-", @cell) = 0, @cell <> ""))

Hyo Jung Yoon - 10PM-C

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

Paul Newcome

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.

Hyo Jung Yoon - 10PM-C

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

Paul Newcome

No worries. Happy to help. 👍️