Simple count formula thats doing my head in.

[Shales]

Hi,

This is very simple. I have a list of numbers in range A1:A100 In B1 i want a count of multipe numbers in A1:A100. So say if there are multiple six 10's and six 20's in A1:A100 I want b1 to show 12.

For some reason I can seem to work this one out. I've tried Countifs and Sumproduct formula but had no luck.

Cheers
Gavin

 

[Yard]

Might be a super clever way of doing it in one cell, but try this:

In C1 and copied down:
=COUNTIF(A$1:A1,A1)

In D1, entered using Ctrl+Shift+Enter and then copied down:
=IF(C1=MAX(IF(A1=$A$1:$A$100,$C$1:$C$100,0)),C1,0)

Then in B1:

=SUMIF(D1:D100,">1")

 

[Yard]

Simpler way:

This in C1 and copied down:
=IF(COUNTIF(A$1:A1,A1)=COUNTIF($A$1:$A$100,A1),COUNTIF($A$1:$A$100,A1),0)

Then in B1:

=SUMIF(C1:C100,">1")

 

[barry houdini]

Try just

=SUMPRODUCT(--(COUNTIF(A1:A100,A1:A100)>1))

 

[Yard]

:blush: