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?

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Try this...

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

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

  • edited 04/26/21

    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!

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

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

  • Paul NewcomePaul 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))

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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.

  • Paul NewcomePaul 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.

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    No worries. Happy to help. 👍️

Sign In or Register to comment.