summing data that match critieria in a rnage

[Guest]

I cannot figure out what function to use to do the following:
For example, if I have data like this:
Criteria
Sale $ # Sales <500
50 3 501 -999
39 1 1000-2499
1000 4 2500+
1200 1
1550 3

I would like to know how to sum all the data in the # sales column according
to the criteria based on Sale $. In other words, what is the total # of
sales that were made < $500, between 501 and 999, between 1000 and 2499, etc.

I'd prefer to do it by putting the upper and lower bounds of the criteria in
2 columns and using that as I want to use these criteria repeatedly to sum
various ranges of data; however, any suggestions at all that will work are
appreciated.

thanks-

 

[Roger Govier]

Hi

Assuming Sale $ is in A, and #Sales is in column B
With Criteria for Limits in cells D25 set as 500, 999, 2499 and 999999
respectively (the last figure needs to be higher than the maxiimum value of
any individual sale)
Enter formula in E2 as follows
=SUMPRODUCT(--($B$2:$B$5000<$D2)*($A$2:$A$5000))-SUMPRODUCT(--($B$2:$B$5000<$D1)*($A$2:$A$5000))
Copy formula down through cells E3:E5

Amending the values in D25 will enable you to alter the ranges summed
across.

 

[Ragdyer]

I'll be darned!
2 questions in a row with exactly the same formula suggestion.<bg>

With size of sales transactions in A1:A100
And number of transactions in B1:B100
And bottom of dollar sales criteria in C1
And top of criteria in D1

Try this:

=SUMPRODUCT((A1:A100>=C1)*(A1:A100<=D1)*B1:B100)

Enter individual dollar amounts to search for in C1 and D1, *not* ranges!

 

[Guest]

Thanks very much! This is great!

 

[Guest]

Thanks for the help. I had seen this function in my search for answer but
couldn't figure out how it worked or if it was the right one to use. This is
great!