summing data that match critieria in a rnage
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- |
Hi
Assuming Sale $ is in A, and #Sales is in column B With Criteria for Limits in cells D2:D5 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 D2:D5 will enable you to alter the ranges summed across. -- Regards Roger Govier "sandyix" wrote in message ... 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- |
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! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "sandyix" wrote in message ... 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- |
Thanks very much! This is great!
"sandyix" wrote: 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- |
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! "Roger Govier" wrote: Hi Assuming Sale $ is in A, and #Sales is in column B With Criteria for Limits in cells D2:D5 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 D2:D5 will enable you to alter the ranges summed across. -- Regards Roger Govier "sandyix" wrote in message ... 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- |
All times are GMT +1. The time now is 04:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com