# Simple count formula thats doing my head in.

#### Shales

##### Board Regular
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

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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")

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")

Try just

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

:blush:

Replies
5
Views
432
Replies
3
Views
179
Replies
4
Views
131
Replies
4
Views
388
Replies
10
Views
729

1,203,205
Messages
6,054,136
Members
444,703
Latest member
pinkyar23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back