Share via

Using COUNTIF function to count all occurances with the exception of a certain value

Anonymous
2010-10-08T16:04:13+00:00

I'm looking to create a COUNTIF function which basically says, "Count all the occurances from this column if every value EXCEPT this one value is present."

So, assuming I am need to count all occurances from column A:A with the exception of the value "RH", what would that formula look like?

Thanks in advance!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2010-10-08T17:02:51+00:00

    I'm looking to create a COUNTIF function which basically says, "Count all the occurances from this column if every value EXCEPT this one value is present."

    So, assuming I am need to count all occurances from column A:A with the exception of the value "RH", what would that formula look like?

    Thanks in advance!

    Try something like this...

    A
    B
    C
    D
    A
    B
    C
    V
    A
    A
    A

    Let's assume that's your data in the range A2:A15. You want to count all cells that aren't empty but want to exclude from the count all cells that contain "A".

    =COUNTIFS(A2:A15,"<>",A2:A15,"<>A")

    That formula will return 6. Assumes the cells are empty and don't contain formula blanks.

    --

    Biff

    Microsoft Excel MVP

    9 people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-10-08T16:13:41+00:00

    Hi,

    This question, to me at least, is confusing

    This suggests

    >I'm looking to create a COUNTIF function which basically says, "Count all the occurances from this >column if every value EXCEPT this one value is present."

    You want this which doesn't count anyting if the value A Value is present

    =IF(NOT(COUNTIF(A1:A20,"A Value")),COUNTA(A1:A20),"")

    But this

    >So, assuming I am need to count all occurances from column A:A with the exception of the value "RH", >what would that formula look like?

    you want this

    =COUNTA(A1:A20)-COUNTIF(A1:A20,"A Value")

    If this post answers your question, please mark it as the Answer.

    Mike H

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2010-10-08T16:52:56+00:00

    Hi

    =SUMPRODUCT(--(A:A<>""),--(A:A<>"RH"))

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2011-08-10T15:20:25+00:00

    What would the formula look like if you wanted to exclude A and B?

    Thanks in advance.

    Marc

    0 comments