Sum Unique & Duplicate Rows Only Once

[gryce]

Hi everyone,

I've got a spreadsheet with thousands of rows of data. We have duplicated some rows of data to make it easier for another group to read through the file. I need to sum up the totals in column B, but I only want to sum unique values and only the first duplicate row from column A.


[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]ColA[/TD]
[TD]ColB[/TD]
[TD]Desired Sum[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]DD[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]DD[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]17[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, Column A has 3 duplicates, and in my third column I'm showing how I'd like to count them (only the first occurrance; they'll always be the same value in column B).

I need to sum the values of column B, but only when column A is either unique, or the first in a series of duplicates.

Thanks for the attention

Brandon

 

[barry houdini]

Try this formula in C2 copied down

=IF(A2=A1,"",B2)

 

[AhoyNC]

This is an array formula that must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B	C
1	ColA	ColB	Desired Sum
2	AA	2	2
3	AA	2
4	BB	3	3
5	CC	2	2
6	DD	5	5
7	DD	5
8	EE	1	1
9	FF	4	4
10	FF	4
11			17
Sheet

 

[AhoyNC]

Barry's formula is much better than mine (I made it way to complicated).

 

[Aladin Akyurek]

If column A groups all items together that is, otherwise the frequency formula would be needed.

 

[gryce]

Everyone,

Thanks so much for the responses! This is close, but I believe I've confused matters by adding that virtual column C. In reality there is a distinction and I'll try to illustrate it below. Column C doesn't exist, I'm actually summarizing column B on another worksheet. Additionally in this example I grouped all of my items together for readability, but in fact the duplicates may be mingled throughout the document, and there can be more than one duplication (in some cases 16 duplicates).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ColA[/TD]
[TD]ColB[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]EE[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]GG[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]JJ[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]II[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[barry houdini]

Ok, assuming data down to row 100 you can use this formula to get a single total, first row value of each repeated text value

=SUM(IF(A2:A100<>"",IF(MATCH(A2:A100,A2:A100,0)=ROW(A2:A100)-ROW(A2)+1,B2:B100)))

confirm with CTRL+SHIFT+ENTER

 

[gryce]

barry, you're a magician! That worked perfectly, thank you so much!