NB.IF.DIFFERENT DE
Solved
nicolas4107
Posted messages
3
Status
Membre
-
Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention -
Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention -
Hello,
I have a problem with the Excel formula COUNTIFS.
I want to do what is different from 20-12, so I write the following value:
....;"<>20-12")
However, Excel interprets this as anything different from 8. (20 - 12)
Does anyone have a solution?
I have a problem with the Excel formula COUNTIFS.
I want to do what is different from 20-12, so I write the following value:
....;"<>20-12")
However, Excel interprets this as anything different from 8. (20 - 12)
Does anyone have a solution?
1 réponse
Hello
if you write 20-12, it's no surprise that Excel takes that for an 8; there are plenty of mathematicians who have done that before him!
if you want to count the values different from 8 or 20 in the range A1:A100
=COUNTIFS(A1:A100,"<>12",A1:A100,"<>20")
and if your range contains empty cells that you do not want to count:
=COUNTIFS(A1:A100,"<>12",A1:A100,"<>20",A1:A100,"<>")
But in the end, if it's a text that you want to exclude:
=COUNTIF(A1:A100,"<>20-12")
kind regards
To err is human, to persist is diabolical
if you write 20-12, it's no surprise that Excel takes that for an 8; there are plenty of mathematicians who have done that before him!
if you want to count the values different from 8 or 20 in the range A1:A100
=COUNTIFS(A1:A100,"<>12",A1:A100,"<>20")
and if your range contains empty cells that you do not want to count:
=COUNTIFS(A1:A100,"<>12",A1:A100,"<>20",A1:A100,"<>")
But in the end, if it's a text that you want to exclude:
=COUNTIF(A1:A100,"<>20-12")
kind regards
To err is human, to persist is diabolical
It is indeed a text that I want to exclude.
I just tried "<>"&20-12, but Excel still considers it an 8!
I've never dealt with it like this!!!??
But strangely, if this time I didn't make a mistake, the formula correctly counts the number of 20-12 in the list with:
COUNTIF(A1:A10,"20-12") and doesn't confuse them with a value of 8
So we can try:
=COUNTA(A1:A100)-COUNTIF(A1:A100,"20-12")
Or more simply:
=SUMPRODUCT((A1:A100<>"20-12")*1)
best regards
=COUNTA(A1:A100)-COUNTIF(A1:A100,"20-12")
This solved my problem, thank you very much!
.. but for me SUMPRODUCT looks cleaner
Safe travels
best regards