SUM and COUNT

Ellasheba

New Member
Joined
Jun 1, 2016
Messages
11
Hello
I would like to be able to understand how to identify a count of unique numbers in one column and then look to a second column and count with an entry in to return a count.
I have been using some functions but cannot work out how to combine the two to provide the answer I am looking for. It is almost a grouping type function???
thank you

Book1a.xlsx
ABCDE
11
21
31
41
5105/01/2012
61
7101/01/2012
82
92
10202/02/2013
112
122
133
143
153
163
173
183
1933Answer I am looking for 2
Sheet2
Cell Formulas
RangeFormula
A19A19=SUM(1/COUNTIF(A1:A18,A1:A18))
B19B19=COUNTA(B1:B18)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Thanks, Jason. I was taking a similar approach but couldn't quite make it work. I revised the column A formula to avoid the array formula:
MrExcel20210203.xlsx
AB
11
21
31
41
5101/05/12
61
7101/01/12
82
92
10202/02/13
112
122
133
143
153
163
173
183
1932
Ellasheba
Cell Formulas
RangeFormula
A19A19=SUMPRODUCT(1/COUNTIF(A1:A18,A1:A18))
B19B19=SUMPRODUCT(--(FREQUENCY(IF(B1:B18<>"",A1:A18),A1:A18)>0))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top