CODEWITHSUNDEEP
excelexcel-formula
Revokez
Revokez

Reputation: 323

Sum column B until there is any change in column A

I'm trying to create a formula that does a sum of column B until there is any change in column A. E.g.

    Col A   Col B

1     X       2
2     X       1
3     X       0
4     X       2
5     Y       1

Starting at the point of A1, this would then show the sum of 5. Column A in this situation would always be sorted (X, followed by Y, followed by Z etc.)

Upvotes: 2

Views: 10022

Answers (2)

Paulo Araújo
Paulo Araújo

Reputation: 27

I guess this question is long ago answered, but I'd like to share my solution;

Like @Gary's Student showed, i suggest a similar way. You could also go with a IF + SUMIF

With data in cols A and B, in C2 enter:

=IF(A2<>A3,SUMIF(A:A,A2,B:B),"")

image

OBS.: The data needs to be sorted before using the formula.

I hope it help other people!

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96781

With data in cols A and B, in C2 enter:

=IF(A3<>A2,SUM($B$2:B2)-SUM($C$1:C1),"")

and copy down:

enter image description here

EDIT#1:

This approach requires:

  1. the tags in column A must be sorted into groups
  2. row #1 is unused for data

Upvotes: 6

Related Questions