CODEWITHSUNDEEP
excel
white_flag
white_flag

Reputation: 71

Sub sum the same item

I have 2 columns:

A             B
apple_type1   25
apple_type1   15
apple_type1   5
pears_type1  10
pears_type1  3
apple_type2   5
apple_type2   15

It is posible to Subsum the column B (without deleting the rows, whithout filters, without Pivot table) like:

apple_type1   0
apple_type1   0
apple_type1   45
pears_type1  0
pears_type1  13
apple_type2   0
apple_type2   20

Thank you!

Upvotes: 1

Views: 50

Answers (2)

Jerry
Jerry

Reputation: 71578

If column A is sorted, then you could also use =IF(A1=A2,0,SUMIF(A:A,A2,B:B)) copied down:

enter image description here

This would be faster than using COUNTIF within the same formula, referring to Error 1004's answer (COUNTIF and SUMIF all need to 'look' at the range, so having two of these in the same formula will consume twice as much resources, but it has the advantage that it doesn't require column A to be sorted).

If you need to refresh the data a lot of times (new information got added for example), then I would advise sorting then using the formula I proposed.

Upvotes: 2

Error 1004
Error 1004

Reputation: 8230

Modify the formula and try:

=IF(COUNTIF(A3:$A$9,A2)>0,0,SUMIF($A$2:$A$8,A2,$B$2:$B$8))

Results:

enter image description here

Upvotes: 1

Related Questions