If Statement/Lookup/Help

[Peggi Stabler]

I have 2 lists, one is for pipe diameter and one is for
pipe length. I need to sum the lengths according to
diameter and they can not be sorted any differently than
they are due to other parameters in the worksheet, so I'm
stuck with something like this:

"AL" "AM"
length size
18.2 18
274.0 24
48.5 18
185.7 24
295.0 30
65.5 18
277.1 30
187.4 36
99.1 18
250.4 36

What I want to end up with is two columns at the bottom of
the list that shows the size and the sum:

total length 18"
total length 24"
total length 30"
total length 36"

I thought maybe an IF Statement with some sort of lookup,
but wasn't sure how to have it calculate the sum.
I want the formula to search AM1:AM10 for all '18' and
then use the values in AL1:AL10 to sum them, then go on to
find '24' and sum those, etc.

Any ideas?

Thanks,
Peggi Stabler

 

[Don Guillett]

Have a look in help INDEX for SUMIF

 

[JulieD]

Hi Peggi

you can use the SUMIF function
=SUMIF(Range to check, criteria, range to sum)

so in your case
=SUMIF($AM$1:$AM$100,18,$AL$1:$AL$100)
=SUMIF($AM$1:$AM$100,24,$AL$1:$AL$100)
=SUMIF($AM$1:$AM$100,30,$AL$1:$AL$100)
=SUMIF($AM$1:$AM$100,36,$AL$1:$AL$100)

where your list starts in row 1 and ends in row 100 - change to suit.

of if you (at the bottom of the sheet, say row 101) have

AL AM
101 18 =SUMIF($AM$1:$AM$100,AL101,$AL$1:$AL$100)
102 24 =SUMIF($AM$1:$AM$100,AL102,$AL$1:$AL$100)
103 30 =SUMIF($AM$1:$AM$100,AL103,$AL$1:$AL$100)
104 36 =SUMIF($AM$1:$AM$100,AL104,$AL$1:$AL$100)

Hope this helps

Cheers
JulieD

 

[gilbert]

Hi Peggy,

you may consider using SUMPRODUCT....


Please see
http://www.excelforum.com/showthread.php?s=&postid=455681#post455681
for detailed sumproduct explanation.

Hope that helps.


Rgds,
Gilbert

 

[Guest]

Go in DATA use the SUBTOTAL option and select the Diameter as your subtotal setting. it will give you what you are looking for.

Good luck

 

[JulieD]

Hi Frank

the OP mentioned that she can't resort the data - it is never a good idea to
use subtotal without sorting by the "grouping" field first.

Cheers
JulieD

Go in DATA use the SUBTOTAL option and select the Diameter as your

subtotal setting. it will give you what you are looking for.

 

[Peggi Stabler]

SUMIF works great! Thanks so much!
(I love these newsgroups--they always help!)

:^)
Peggi