CODEWITHSUNDEEP
excelexcel-formula
Hooplator15
Hooplator15

Reputation: 1550

Sum duplicate values in excel

I have a data set with this structure:

col A   
 11
 11
 9
 9 
 9
 7
 7
 2
 11
 11
 7
 7

I would like to sum up all group repeating entries in column A. In other words, I want to add 11+9+7+2+11+7 without adding a helper column. I already tried the following formula:

=SUM(IF(FREQUENCY(A1:A12,A1:A12),A1:A12))

This formula omits ALL repeating values. I only would like to omit GROUPS of repeating data. I want the sum to equal 47.

I also tried:

=IF(NOT(A1=A2),SUM(A1:A12),0)

Then summing down the column but I would rather have one concise formula as I cannot add another column to my sheet.

Does anyone know a way to sum the group repeating numbers? Thank you.

Upvotes: 2

Views: 4303

Answers (2)

Ken
Ken

Reputation: 1166

=SUMPRODUCT((OFFSET(A2,0,0,COUNT(A:A),1)<>OFFSET(A2,-1,0,COUNT(A:A),1))*OFFSET(A2,0,0,COUNT(A:A),1))

Covers if you add additional rows of data.

EDIT

Based on comment from @Jerry regarding OFFSET being volitile, I have revised the formula:

=SUMPRODUCT((A2:INDEX(A:A,1+COUNT(A:A))<>A1:INDEX(A:A,COUNT(A:A)))*A2:INDEX(A:A,1+COUNT(A:A)))

Upvotes: 2

Jerry
Jerry

Reputation: 71538

You could use an array formula like this:

=SUM(IF(A1:A12=A2:A13,0,A1:A12))

Entered with Ctrl+Shift+Enter

Or with SUMPRODUCT to do the condition:

=SUMPRODUCT((A1:A12<>A2:A13)*A1:A12)

Upvotes: 1

Related Questions