Excel - Running total with IF statements

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.

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

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


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




--
BDAvs

Hi,

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


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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank you Claus!  That's a big help and I am getting closer.
Now I run the problem that once it realizes the pallet change, it
continues to assign more pallets after.  In the example below...the last
half should be on the same pallet 61812007.  The running total in AL is
fine until it hits the 150 limit, then it resets and does not add from
the previous line(s).  Once a new customer begins the problem goes
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!

BD




--
BDAvs