Conditional summing

[Michel Khennafi]

Good morning,

I have a list of transactions in a file.. Each transaction refers to a
weight shipped.

I would like to count the number of shipments between:
- 0 and 1000 Lbs
- 1001 lbs and 1500 Lbs
- and so on

The weight limits are in a table A1: B10... I would like to have the result
by weight rance in cells C1 to C10.
What would be the formula to use in order to do so? I tried countif and
sumif and got no good results...

Thanks

 

[Don Guillett]

sample layout

 

[PCLIVE]

I'm not sure exactly what you have in column B. If you have shipping
weights in both A and B, then perhaps:

=SUMPRODUCT(--(A1:B10>=0),--(A1:B10<=1000))
=SUMPRODUCT(--(A1:B10>=1001),--(A1:B10<=1500))
and so on.

HTH,
Paul

 

[Bernard Liengme]

In A1:B3 I have your table
0 1500
1501 2000
2001 2500

In F1:F5 (but it could be anywhere) I have a random list of numbers (0 to
2500)
In C1 I have the formula =SUMPRODUCT(--($F$1:$F$25>=A1),--($F$1:$F$25<=B1))
This I copy down to C2 and C3

I could get aways without the B values
=SUMPRODUCT(--($F$1:$F$25>=A1),--($F$1:$F$25<A2))
but I would need an extra value at the bottom of A

For more details on SUMPRODUCT
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.htm
best wishes
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email