If Statement/Lookup/Help

  • Thread starter Thread starter Peggi Stabler
  • Start date Start date
P

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
 
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
 
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
 
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

Frank said:
Go in DATA use the SUBTOTAL option and select the Diameter as your
Click to expand...
subtotal setting. it will give you what you are looking for.
 
SUMIF works great! Thanks so much!
(I love these newsgroups--they always help!)

:^)
Peggi
 
Back
Top