Formula to produced a Cumulative Delivered/Ordered figure

[Mark]

I have a spreadsheet that I collate orders during the month from a customer,
the spreadsheet contains the volume they order in one column and the volume
actually delivered in another. I need to keep track during the month of how
much they have ordered/received to ensure they remain within their product
allocation for the month.

eg:

Site Ordered Delivered
A 36,000 35,999
A 18,000 18,500
B 36,000 36,000
A 36,000 36,001

What I want to do is create a formula that will take the DELIVERED
information to give me a running total, where there is no DELIVERED
information (eg the order has not yet been delivered) I want it to include
the ORDERED quantity. This will give me as up to date a figure to compare
against the allocation for that customer during the month.

But.......I need some advice on how to include the ORDERED quantity in the
formula as there will not be a DELIVERED figure on the spreadsheet until the
actual delivery has taken place.

 

[Roger Govier]

Hi Mark

One way
Insert a new column D with the formula
=IF(C2="",B2,C2)
Copy down the page

Then, in say column G, enter a list of Sites, in G1 downward
In H1 enter
=SUMIF(A:A,G1,D)and copy down as required

 

[Jacob Skaria]

With data in ColA,B,C...Row1 with headers; try the below formula in cell D2
and copy down as required...

=SUMIF($A$2:A2,A2,$C$2:C2) & " / " & SUMIF($A$2:A2,A2,$B$2:B2)

OR

=SUMIF($A$2:A2,A2,$C$2:C2)-SUMIF($A$2:A2,A2,$B$2:B2)

 

[Stefi]

=SUMPRODUCT(--($A$2:$A$6=E2),--(NOT(ISBLANK(C2:C6))),B2:B6)+SUMPRODUCT(--($A$2:$A$6=E2),--(ISBLANK(C2:C6)),B2:B6)

where
column E (from row 2 downwards) contains the sites (A,B,...)


--
Regards!
Stefi



„Mark†ezt írta: