Count Sum Problem

  • Thread starter Thread starter Day4Purchasing
  • Start date Start date
D

Day4Purchasing

Hello,

Long time reader, first time poster. Hope I can find some insights.

Using the following sample table I want to be able to count the number
of times, and when, a total of 40 is reached in the Qty column. And
then with the remaining amount begin the count to 40 again. For
example, summing all the cells from 9.0AD to the 14.6AD row would
result in the first 40 - with 5.51 left. That 5.51 would then be added
to the 114.8 to make 120.31 - and another 3 x 40's with 0.31 added to
the 15.0AD line :

Shipment Qty 40's
9.0 AD 0.00
11.0 AD 0.00
12.0 AD 0.46
13.0 AD 2.76
13.5 AD 0.92
13.8 AD 1.38
14.1 AD 7.35
14.4 AD 19.29
14.6 AD 37.65 1
14.8 AD 114.80 3
15.0 AD 297.55
15.2 AD 1057.04
15.4 AD 1280.20
15.6 AD 927.55
15.8 AD 383.42
16.0 AD 223.62
16.2 AD 182.76
16.4 AD 54.64
4591.38 114.78

Not sure where to begin with this? Count + Sum +?

If I can clarify please advise.

Thanks,

Shane
 
Hi Shane

I'm not sure if your sample table reflects your example you wrote in
words, because i get a different result, but following formula should
do what you want.
I assumed you have row 1 as titlerow and start with A2, so you put
following formula in C2 (the 40's column, first data cell):
=IF(B2>40,FLOOR(B2/40,1),"")
and for cells C3 and downwards:
=IF(SUM($B$2:B3)-SUM($C$2:C2)*40>=40,FLOOR((SUM($B$2:B3)-SUM($C
$2:C2)*40)/40,1),"")

maybe someone can shorten it, but it works fine with me.

Cheers
Carlo
 
Shane, your quantity total / 40 is your forty's value. I'm assuming this is
how you got that number.

However, if you just sum from the beginning and divide by forty you will
have the Forty column

Assuming Shipment in A1,
C2 =INT(B2/40)
C3 =INT(SUM(B$2:B3)/40)-SUM(C$2:C2)
Fill that down to the bottom to get your forty

For the total, just ignore column C and divide Sum(B) by forty.

HTH, Bob
 
Back
Top