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
 

Some videos you may like

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
Joined
Sep 9, 2004
Messages
5,432
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
Joined
May 30, 2002
Messages
26
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
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,627
Messages
5,523,985
Members
409,551
Latest member
WillCaton

This Week's Hot Topics

Top