Summing a value based on another field changing

rhinogirl

New Member
Joined
May 30, 2002
Messages
26
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


Thanks in advance
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
 
Upvote 0
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
1303-SepHelenBarcadi120
1403-SepHelenBarcadi1325
Sheet1
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top