Hi, I would like to ask how can I display a result of counting distinct cells.
Counting the Cells with "NO" but if it has the same number, it is not included in the count anymore..
What formula could I use?
TIA!
Hi, I would like to ask how can I display a result of counting distinct cells.
Counting the Cells with "NO" but if it has the same number, it is not included in the count anymore..
What formula could I use?
TIA!
Try:
=COUNT(1/FREQUENCY(IF(A1:A5="NO",IF(B1:B5<>"",MATCH(B1:B5,B1:B5,0))),ROW(B1:B5)-ROW(B1)+1))
confirmed with CTRL+SHIFT+ENTER not just ENTER
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi bronkista and welcome to the forum,
With 3 helper columns I was able to give you an answer. I'll bet one of the smart gurus can do it in a single but harder formula. See the attached.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thanks NBVC,
though I think this formula is intended to find only those with a number 190.
What I'm actually looking for is that, the number 170 is again repeated with a NO, it won't be included in the count...
and if another number, say 160 is added with a NO, the count would increase to 3.
That is what the formula will do. It is only looking for "NO" in column A and then counting the distinct numbers in column B.
Make sure after you enter the formula you hold the CTRL and SHIFT keys down and then hit ENTER...
If you add a NO...160, you will see the count change.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks