Suming duplicate values

[dmajcan]

Hello there.

I've been lurking MrExcel for quite some time, and you guys helped me so much ( unfortunately my excel skills are not good enough to return the favor ). Now I have a problem for which I can't find an answer.

The sheet has approx 4500 rows, with 300-400 values that are duplicated ( some 10, some 15-20 times ), I need to count column D for each value.

I started doing it by hand, sorted sheet by column C and insert a blank row behind each value, and SUM it up ( rows 17 and 27 ) but there must be a faster way to do it.

Thank you in advance for your help

 

[Toadstool]

Hi DMAJcan,

I'm not clear on how you want the results shown or if it's just column C which need to be counted, or if it's actually a SUM of column D you want so take a look at this and see if it helps. It does assume you have sorted all duplicates together.

DMAJcan.xlsx
	A	B	C	D	E	F
1	Stuff	More Stuff	Key to COUNT or SUM	Sum of Something	Result Count	Result Sum
2	Dog	Doggy	123	18
3	Dog	Doggy	123	5
4	Dog	Doggy	123	3
5	Dog	Doggy	123	8	4	34
6	Goat	Goaty	222	2	1	2
7	Sheep	Sheepy	333	8
8	Sheep	Sheepy	333	15	2	23
9	Cat	Catty	456	7
10	Cat	Catty	456	18
11	Cat	Catty	456	6
12	Cat	Catty	456	12
13	Cat	Catty	456	8
14	Cat	Catty	456	14
15	Cat	Catty	456	6	7	71
16
Sheet1

Cell Formulas
Range	Formula
E2:E16	E2	=IF(C2=C3,"",COUNTIF($C$2:$C$5000,C2))
F2:F16	F2	=IF(C2=C3,"",SUMIF($C$2:$C$5000,C2,$D$2:$D$5000))

 

[ebea]

It's also not fully clear for me, what you want to count, and how. But here's another way, with Toadstool's setup:

Mappe1
	A	B	C	D	E	F
1	Stuff	More Stuff	Key to COUNT or SUM	Sum of Something	Extract numbers	Count sum
2	Dog	Doggy	123	3858889290619	3858889290619	14
3	Dog	Doggy	123	3858889290619	0	0
4	Dog	Doggy	123	3858889290619	3858889290626	9
5	Dog	Doggy	123	3858889290619
6	Goat	Goaty	222	3858889290619
7	Sheep	Sheepy	333	3858889290619
8	Sheep	Sheepy	333	3858889290619
9	Cat	Catty	456	3858889290619
10	Cat	Catty	456	3858889290619
11	Cat	Catty	456	3858889290619
12	Cat	Catty	456	3858889290619
13	Cat	Catty	456	3858889290619
14	Cat	Catty	456	3858889290619
15	Cat	Catty	456	3858889290619
16
17				3858889290626
18				3858889290626
19				3858889290626
20				3858889290626
21				3858889290626
22				3858889290626
23				3858889290626
24				3858889290626
25				3858889290626
Ark1

Cell Formulas
Range	Formula
E2:E16	E2	=IFERROR(INDEX($D$2:$D$25,MATCH(0,COUNTIF($E$1:E1,$D$2:$D$25),0)),"")
F2:F16	F2	=IF(E2<>"",COUNTIF($D$2:$D$25,E2),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[dmajcan]

ToadStool and ebea, thank you for your replies and sorry for not being clear, I have to count column D for each value.

 

[ebea]

ToadStool and ebea, thank you for your replies and sorry for not being clear, I have to count column D for each value.

You should could do that, with the Formulas I provided in #3. You just expand the Range in the formula, to your need (4500 rows).
Just remember, that the formula in Red, should be entered with Ctrl+Shift+Enter

 

[ebea]

You should could do that, with the Formulas I provided in #3. You just expand the Range in the formula, to your need (4500 rows).
Just remember, that the formula in Red, should be entered with Ctrl+Shift+Enter

A little addition to above!

Column E will give you a list of all Unique values in your range.
Column F wil give you the numbers (counts) of these Unique values.

Drag down the Formulas, so long as needed.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

Column F wil give you the numbers (counts) of these Unique values.

Judging from the description & the image it looks as though the OP wants the sum of col D, not the count.

 

[ebea]

Judging from the description & the image it looks as though the OP wants the sum of col D, not the count.

From answer in #4, it is count (of each Value), not Sum.

 

[Fluff]

As I said from the original post

and SUM it up ( rows 17 and 27 )

and the image clearly shows the sums NOT the counts.
I suspect that post#4 is just a problem with translation