Hi
I am attempting a relatively simple task but I’m struggling, and was hoping that someone might have some advice.
Specifically: I would like to count how many times a cell in my data range is NOT the same as my reference cell.
I have a reference cell and a non-numerical data range.
For example
Reference cell A1 = Apple
Data range in B1:F1 = Apple, Apple, Pear, Peach, Apple Apple
The answer to my question is 2 (i.e. Pear and Peach)
I can use the following Countif formula to give me the reciprocal answer (i.e 4)
=Countif(B1:F1,A1) but is there a way of including a NOT logical in there somewhere?
I’ve also fudged it with the following formula taken from previous Chandoo posts:
=SUMPRODUCT(1/COUNTIF(B1:F1,B1:F1))-1
But I think this is a bit clunky….is there a better, simpler way?
Thanks in advance for your help in this matter,
Regards,
Mark
I am attempting a relatively simple task but I’m struggling, and was hoping that someone might have some advice.
Specifically: I would like to count how many times a cell in my data range is NOT the same as my reference cell.
I have a reference cell and a non-numerical data range.
For example
Reference cell A1 = Apple
Data range in B1:F1 = Apple, Apple, Pear, Peach, Apple Apple
The answer to my question is 2 (i.e. Pear and Peach)
I can use the following Countif formula to give me the reciprocal answer (i.e 4)
=Countif(B1:F1,A1) but is there a way of including a NOT logical in there somewhere?
I’ve also fudged it with the following formula taken from previous Chandoo posts:
=SUMPRODUCT(1/COUNTIF(B1:F1,B1:F1))-1
But I think this is a bit clunky….is there a better, simpler way?
Thanks in advance for your help in this matter,
Regards,
Mark