Totalling volumes per box

[Brian]

Howdy All,

I have an inventory spreadsheet that contains 3 piece of pertinent info.

Column A contains Box Number (i.e. 1, 2,3, etc) and there are repeat numbers

Column B contains Number of Pages for given charts contained within the
boxes.

Column C contains Hours.

Example:

A1 = 1, B1 = 745, C1 = 2.5
A2 = 1, B2 = 555, C1 = 1.75
A3 = 2, B3 = 1007, C3 = 3.25
A4 = 2, B4 = 345, C4 = 0.75

I want to get the Total Number of Pages, and Total Hours, per box.
So, in the example I want to know that for Box 1, I had 1300 total pages and
4.25 hours.
For Box 2 I had 1352 total pages and 4.00 hours.

Any ideas?

Thanks,
Brian

 

[Scott]

Try this:

In D1, put and drag down:
=IF(SUMPRODUCT(--($A$1:A1=A1))>1,"Delete",SUMPRODUCT(--(A1:$A$9=A1),B1:$B$9))

In E1, put and drag down:
=IF(SUMPRODUCT(--($A$1:A1=A1))>1,"Delete",SUMPRODUCT(--(A1:$A$9=A1),C1:$C$9))

Scott

 

[Brian]

Thanks a million Scott!
Worked great, I only changed "Delete" to "" so nothing would be in those
cells.

If you have time, could you walk me though the logic of the arguments?

Thanks,
Brian

 

[Scott]

For the explanation of how I used sumproduct, this link does a much
better job than I can: (you can read through it... then i provided a
few extra details about the actual function used)

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Basically, sumproduct can be used for multiple condition testing.
(Which that site goes on to describe in some detail).

For the formula I gave you, it was slightly modified. The first
sumproduct just checks to see if one of the previous lines has had
this box on it. This is done by freezing the first reference, and
allowing the other to change. If the count equals 1, this is the first
occurance, so the calculation needs to be done. If it's greater than
1, the calculation was done on a previous row.

For the second sumproduct, we know this is the first occurance of this
box. So the calculation only needs to look at the rows from the
current row to the end (there are none before it). The first parameter
is the condition, the second is the data we're summing.

For both of these, SUMIF could have been used instead. I tend to use
SUMPRODUCT more frequently because it is more robust.

HTH,
Scott

 

[Brian]

Thanks again, good info!!!

For the explanation of how I used sumproduct, this link does a much
better job than I can: (you can read through it... then i provided a
few extra details about the actual function used)

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Basically, sumproduct can be used for multiple condition testing.
(Which that site goes on to describe in some detail).

For the formula I gave you, it was slightly modified. The first
sumproduct just checks to see if one of the previous lines has had
this box on it. This is done by freezing the first reference, and
allowing the other to change. If the count equals 1, this is the first
occurance, so the calculation needs to be done. If it's greater than
1, the calculation was done on a previous row.

For the second sumproduct, we know this is the first occurance of this
box. So the calculation only needs to look at the rows from the
current row to the end (there are none before it). The first parameter
is the condition, the second is the data we're summing.

For both of these, SUMIF could have been used instead. I tend to use
SUMPRODUCT more frequently because it is more robust.

HTH,
Scott