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

 

[ebea]

As I said from the original post

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

Maybe it's so. But I'm still not clear, out from the picture, because it seems as he collect the same number from the whole range, and sum these counts.
And in my proposal in #3, it will also give him a total (Sum), as it count all the same values in the Range. Similar to what he did in the image he show, where he separated them up, and then sum these, afterwards. I just made a list of all values, and the amount (sum) of these.

 

[Fluff]

Your proposal in post#3 does not give a sum, just a count.
But we will have to wait on the OP for confirmation of what is required.

 

[eastrand]

If you are wanting to sum the totals and remove the duplicate rows, I think your best bet would be to use a pivot table. With Toadstool example, you would have a pivot table like the example below.

More Stuff	Key to COUNT or SUM	Sum of Stuff
Cat	Catty	84​
Dog	Doggy	14​
Goat	Goaty	6​
Sheep	Sheepy	15​
Grand Total		119​

​

 

[dmajcan]

Thanks for your answers. sorry once again for not being clear.

I need SUM of column D, for all same ( duplicate ) values in column C.

Row 17 is what I need ( it's the sum of all 3858889290619 values in column D ), or Row 27 which is the sum of all 3858889290626 values in column D, these two rows I added manually and summed them up, but since there are 4500 rows and about 400 different values in C, it might take some time to do it manually.

@Fluff, thanks for your remark, I added office information in my account information

 

[Fluff]

Thanks for updating your profile.
How about something like

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	District	Ward	County	Population
2	Staffordshire Moorlands	Churnet	Staffordshire	8		Staffordshire	100
3	Stroud	Bisley	Staffordshire	92		West Yorkshire	179
4	Bradford	Wibsey	West Yorkshire	24		Derbyshire	37
5	Dudley	Sedgley	Derbyshire	37		West Midlands	147
6	Richmondshire	Swaledale	West Midlands	38		County Durham	25
7	Birmingham	Quinton	County Durham	10		Devon	20
8	County Durham	Esh and Witton Gilbert	Devon	20		North Yorkshire	11
9	West Devon	Drewsteignton	West Yorkshire	2		Surrey	168
10	Leeds	Guiseley and Rawdon	North Yorkshire	6		South Yorkshire	33
11	Harrogate	Washburn	West Yorkshire	15		Lancashire	198
12	Bradford	Clayton and Fairweather Green	Surrey	44		Dorset	3
13	Tandridge	Tatsfield and Titsey	North Yorkshire	5		Somerset	5
14	Derbyshire Dales	Hulland	West Yorkshire	60		Greater Manchester	32
15	Craven	Ingleton and Clapham	Surrey	124		Hampshire	13
16	Kirklees	Lindley	South Yorkshire	30		Cornwall	9
17	Mole Valley	Box Hill and Headley	Lancashire	77
18	Sheffield	Crookes and Crosspool	West Yorkshire	1
19	Cannock Chase	Rawnsley	County Durham	13
20	Pendle	Brierfield	West Midlands	109
21	Bradford	Worth Valley	County Durham	2
22	County Durham	Evenwood	Dorset	3
23	Birmingham	Longbridge	South Yorkshire	3
24	County Durham	Consett North	Somerset	5
25	West Dorset	Maiden Newton	Lancashire	91
26	Forest of Dean	Cinderford East	West Yorkshire	77
27	Sheffield	Park and Arbourthorne	Greater Manchester	32
28	Taunton Deane	Blackdown	Lancashire	30
29	Rossendale	Longholme	Hampshire	13
30	Bradford	Bolton and Undercliffe	Cornwall	9
Main

Cell Formulas
Range	Formula
F2:F16	F2	=UNIQUE(FILTER(C2:C9990,C2:C9990<>""))
G2:G16	G2	=SUMIFS(D:D,C:C,F2#)
Dynamic array formulas.

 

[dmajcan]

@Fluff ?‍?, awesome, it worked. Thank you so much.

Do you have a suggestion for renaming this topic so other users can find this solution more easily?

Thanks once again, you saved me few hours of work

Best Regards

 

[Fluff]

Glad we could help & thanks for the feedback.
I have changed the title as well.