Running total with IF statements

[BDAvs]

Scenario
Our company has a customer send orders with multiple products an
destinations each day. We have to palletize and assign a pallet numbe
based on the destination
Variables
- Column N has customers: Customers are palletized seperately
- Column AK has points assign based on the size of the product. N
pallet can hold over 150 points
- Column AL is my attempt to have a running total that resets itsel
after a new customer name begins or a customers quantity hits 150.
(this is the step I need help with
CURRENT
=IF(OR((N5=N4)),SUM(AK$5:AK5),AK5
I have gotten the first cell of the new customer to work, but the su
function keeps summing up from the first cell because of A$5. How do
get it to recognize the first cell for a new customer e.g. CELL A33 an
beginning running a sum from there? When new pallet begins, I want th
sum to go 1, 2, 3, 5, etc - not 1, 176, 177, 178, 180, etc

- Column AM is the contains a formula that palletizes orders - this wil
be updated once I get a correct running total figured

Each day we receive an order - I should be able to paste any order int
excel and have it calculate, this is why I need column AL to have a se
formula for any given order
N AK AL A
CUST. PTS SUM PALLE
C1 _02 1.0 140.0 6181200
C1 _02 1.0 141.0 6181200
C1 _02 1.0 142.0 6181200
C1 _02 1.0 143.0 6181200
Z3 _03 8.0 8.0 6181200
Z3 _03 8.0 159.0 6181200
C2 _02 1.0 1.0 6181200
C2 _02 1.0 161.0 6181200
C2 _02 1.0 162.0 6181200
C2 _02 1.0 163.0 6181200
C2 _02 1.0 164.0 6181200
C2 _02 1.0 165.0 6181200
C2 _02 1.0 166.0 6181200
C2 _02 2.0 168.0 6181200
C2 _02 2.0 170.0 6181200
C2 _02 2.0 172.0 6181200
C2 _02 2.0 174.0 6181200
Z2 _03 1.0 1.0 6181200
Z2 _03 1.0 176.0 6181200
Z2 _03 1.0 177.0 6181200
Z2 _03 1.0 178.0 6181200
Z2 _03 1.0 179.0 6181200
Z2 _03 1.0 180.0 6181200

Please let me know if you have any more clarifying questions. Thanks i
advance and good luck

 

[Claus Busch]

Hi,

Am Thu, 14 Jun 2012 23:30:02 +0000 schrieb BDAvs:

Scenario:
Our company has a customer send orders with multiple products and
destinations each day. We have to palletize and assign a pallet number
based on the destination.
Variables:
- Column N has customers: Customers are palletized seperately.
- Column AK has points assign based on the size of the product. No
pallet can hold over 150 points.
- Column AL is my attempt to have a running total that resets itself
after a new customer name begins or a customers quantity hits 150.
(this is the step I need help with)
CURRENT:
=IF(OR((N5=N4)),SUM(AK$5:AK5),AK5)
I have gotten the first cell of the new customer to work, but the sum
function keeps summing up from the first cell because of A$5. How do I
get it to recognize the first cell for a new customer e.g. CELL A33 and
beginning running a sum from there? When new pallet begins, I want the
sum to go 1, 2, 3, 5, etc - not 1, 176, 177, 178, 180, etc.

try:
=IF(N5=N4,SUMIF($N$2:N5,N5,$AK$2:AK5),AK5)


Regards
Claus Busch

 

[BDAvs]

Thank you Claus! That's a big help and I'm getting closer.
Now I run the problem that once it realizes the pallet change, i
continues to assign more pallets after. In the example below...the las
half should be on the same pallet 61812007. The running total in AL i
fine until it hits the 150 limit, then it resets and does not add fro
the previous line(s). Once a new customer begins the problem goe
away...until it hits the 150 limit.

CURRENT FORMULA:
=IF(AND((N6=N5),(M6=M5),(SUMIF($N$2:N6,N6,$AK$2:AK6)<=150)),SUMIF($N$2:N6,N6,$AK$2:AK6),IF((SUMIF($N$2:N6,N6,$AK$2:AK6))<150,SUMIF($N$2:N6,N6,$AK$2:AK6),AK6))

N AK AL AM
LN CUST PTS TOTAL PALLET
05 C2 _02 1.0 143.0 61812006
06 C2 _02 2.0 144.0 61812006
07 C2 _02 2.0 146.0 61812006
08 C2 _02 2.0 148.0 61812006
09 C2 _02 2.0 150.0 61812006
10 Z2 _03 1.0 1.0 61812007
11 Z2 _03 1.0 1.0 61812008
12 Z2 _03 2.0 2.0 61812009
13 Z2 _03 1.0 1.0 618120010
14 Z2 _03 1.0 1.0 618120011
15 Z2 _03 1.0 1.0 618120012

WANT:
N AK AL AM
LN CUST PTS TOTAL PALLET
05 C2 _02 1.0 143.0 61812006
06 C2 _02 2.0 144.0 61812006
07 C2 _02 2.0 146.0 61812006
08 C2 _02 2.0 148.0 61812006
09 C2 _02 2.0 150.0 61812006
10 Z2 _03 1.0 1.0 61812007
11 Z2 _03 1.0 2.0 61812007
12 Z2 _03 2.0 4.0 61812007
13 Z2 _03 1.0 5.0 61812007
14 Z2 _03 1.0 6.0 61812007
15 Z2 _03 1.0 7.0 61812007


Thanks for any help!

B