don't want countif to return lass than 2

still learning · Aug 31, 2014

Hi
I’m trying to see how many times a number shows up in a running column as I record it

I need it to not show anything if the number is unique, If it’s a duplicate once, the result should be 2, if a duplicate twice it should be 3, etc.

I also want a blank cell if nothing is in A... if(a11="","",countif if the result is greater than 1)

if I use the countif formula, a get a 1 for a unique number

This is what I have so far. It returns a “false”

HTML:

=IF(OR(A11=""),IF(OR(COUNTIF(A1:A11,A11<2,""),COUNTIF(A1:A11,A11)))

Peter_SSs · Aug 31, 2014

Try
=IF(OR(A11="",COUNTIF(A1:A11,A11)=1),"",COUNTIF(A1:A11,A11)-1)

still learning · Aug 31, 2014

Hi Pete

Hope your having a good weekend.

I tried the formula where A11 was blank, it returned a blank cell, which is good

I tried the formula where I had one duplicate and it returned 1, which is good

When I made 3 duplicates of the same number, it returned one less (2).

Each extra duplicate would return one less.

I need a return of the actual number of duplicates with a blank cell if the number is not duplicate

mike

Peter_SSs · Aug 31, 2014

That explanation is still not all that clear to me but if you remove the "-1" at the end of my formula does it do what yo want?

If not, please explain without using the word "duplicates" just refer to how many time the value occurs in the range and what the result should be. For example, if the value in A11 occurs 3 times in A1:A11 the result should be 3. Etc

still learning · Sep 2, 2014

Hi Pete

I removed the -1 at the end and it works perfect.

Your example is right. What I wanted was not to have a 1 show if the value only showed once.

Thank you

Mike

Peter_SSs · Sep 2, 2014

OK, that's good, thanks for letting us know.

don't want countif to return lass than 2

still learning

Well-known Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

still learning

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

still learning

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

don't want countif to return lass than 2

still learning

Well-known Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

still learning

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

still learning

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock