# Summing a value based on another field changing

#### rhinogirl

##### New Member
Hello

I have the following in a workbook and i am wanting the values in column C to be caluculated by a formula. So the formula will look to see if the date has changed from the cell above, if it is and the cell below is different it will add all those with the same date together, if not nothing (or a zero) will be entered.
Book2
ABCD
25DayValueDailyTotal
2601-Sep1
2701-Sep2
2801-Sep36
2902-Sep4
3002-Sep59
3103-Sep66
Sheet1

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Andrew Fergus

##### MrExcel MVP
Hi
Try using the following formula in cell C26 and copy down :

=SUMPRODUCT(B\$26:B26,--(A\$26:A26=A26))*(--(A26<>A27))

Format the cell to a Custom format of :
0;;

HTH, Andrew

#### rhinogirl

##### New Member
Thank you it works perfect.

Can you explain how the formula works for me.

#### rhinogirl

##### New Member
The reason i am asking is that i have two other columns and if they change then the process needs to start again. This may explain better.
Book2
ABCDE
1DateFactor1Factor2QtyFormula
201-SepSteveWine10
301-SepSteveWine20
401-SepSteveWine36
502-SepSteveWine40
602-SepSteveWine59
703-SepSteveWine66
801-SepSteveBeer70
902-SepSteveBeer80
1002-SepSteveBeer924
1101-SepHelenVodka100
1202-SepHelenVodka1121
Sheet1

#### Andrew Fergus

##### MrExcel MVP
Hi
Try using this in cell E2 and copy down :

=SUMPRODUCT(D\$2:D2, (--(A\$2:A2=A2)), (--(B\$2:B2=B2)), (--(C\$2:C2=C2))) * MAX(--(A2<>A3),--(B2<>B3),--(C2<>C3))

Shouldn't cell E11 contain the value 10? And cell E12 11? Also, cell E8 should be 7 and E10 17 (all due to different dates).

The sumproduct part of the formula is adding up quantities where all of the criteria match (like a sumif but with multiple criteria) and the Max part is checking to see if columns A, B, C and match the next row - if they do then the total is not shown (because it is multiplied by 0) but if any one of the columns doesn't match, then the Max(...) function returns a 1 and the subtotal is shown.

HTH, Andrew

##### MrExcel MVP
Hi
Try using this in cell E2 and copy down :

=SUMPRODUCT(D\$2:D2, (--(A\$2:A2=A2)), (--(B\$2:B2=B2)), (--(C\$2:C2=C2))) * MAX(--(A2<>A3),--(B2<>B3),--(C2<>C3))

...

Can be shortened to...

=SUMPRODUCT(D\$2:D2,--(A\$2:A2=A2),--(B\$2:B2=B2),--(C\$2:C2=C2))*((A2<>A3)+(B2<>B3)+(C2<>C3)>0)

Replies
0
Views
25
Replies
2
Views
30
Replies
1
Views
41
Replies
3
Views
82
Replies
2
Views
48