# 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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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

Thank you it works perfect.

Can you explain how the formula works for me.

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

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

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
1
Views
127
Replies
3
Views
186
Replies
2
Views
221
Replies
5
Views
349
Replies
3
Views
121

1,219,935
Messages
6,151,065
Members
451,006
Latest member
dhinze84

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back