| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
total amount based on selections of 1-9, 10-19 and 20-30
I am trying to calculate a total based on the number of items a person
purchases that fall within one of these three amounts (1-9,10-19,20-30). Can anyone help |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
total amount based on selections of 1-9, 10-19 and 20-30
=SUMPRODUCT(--(A1:A100=1),--(A1:A100<=9),B1:B100)
=SUMPRODUCT(--(A1:A100=10),--(A1:A100<=19),B1:B100) =SUMPRODUCT(--(A1:A100=20),--(A1:A100<=30),B1:B100) "Pete Elbert" wrote: I am trying to calculate a total based on the number of items a person purchases that fall within one of these three amounts (1-9,10-19,20-30). Can anyone help |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
total amount based on selections of 1-9, 10-19 and 20-30
One way (though your problem statement is rather vague):
=CHOOSE(INT(A1/10)+1,"Total 1", "Total 2", "Total 3", "Total 3") (the last "Total 3" is necessary since your third range is bigger than the first two). In article , Pete Elbert wrote: I am trying to calculate a total based on the number of items a person purchases that fall within one of these three amounts (1-9,10-19,20-30). Can anyone help |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
total amount based on selections of 1-9, 10-19 and 20-30
Thank you for your response. I will try it out and get back with you.
"Teethless mama" wrote: =SUMPRODUCT(--(A1:A100=1),--(A1:A100<=9),B1:B100) =SUMPRODUCT(--(A1:A100=10),--(A1:A100<=19),B1:B100) =SUMPRODUCT(--(A1:A100=20),--(A1:A100<=30),B1:B100) "Pete Elbert" wrote: I am trying to calculate a total based on the number of items a person purchases that fall within one of these three amounts (1-9,10-19,20-30). Can anyone help |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
total amount based on selections of 1-9, 10-19 and 20-30
Thank you for your response. I will try it out and get back with you.
"JE McGimpsey" wrote: One way (though your problem statement is rather vague): =CHOOSE(INT(A1/10)+1,"Total 1", "Total 2", "Total 3", "Total 3") (the last "Total 3" is necessary since your third range is bigger than the first two). In article , Pete Elbert wrote: I am trying to calculate a total based on the number of items a person purchases that fall within one of these three amounts (1-9,10-19,20-30). Can anyone help |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
total amount based on selections of 1-9, 10-19 and 20-30
Using this formula makes it work just the way I want it to. One issue that I
am not sure how to add into the formula is when cell A1 is empty or has a zero in it, I would like the cell that provides the total not to show anything or just a zero value. Otherwise the formula is just what I was looking for. Any solutions? "JE McGimpsey" wrote: One way (though your problem statement is rather vague): =CHOOSE(INT(A1/10)+1,"Total 1", "Total 2", "Total 3", "Total 3") (the last "Total 3" is necessary since your third range is bigger than the first two). In article , Pete Elbert wrote: I am trying to calculate a total based on the number of items a person purchases that fall within one of these three amounts (1-9,10-19,20-30). Can anyone help |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
total amount based on selections of 1-9, 10-19 and 20-30
One way:
=IF(A1=0,"",CHOOSE(...)) In article , Pete Elbert wrote: Using this formula makes it work just the way I want it to. One issue that I am not sure how to add into the formula is when cell A1 is empty or has a zero in it, I would like the cell that provides the total not to show anything or just a zero value. Otherwise the formula is just what I was looking for. Any solutions? "JE McGimpsey" wrote: One way (though your problem statement is rather vague): =CHOOSE(INT(A1/10)+1,"Total 1", "Total 2", "Total 3", "Total 3") (the last "Total 3" is necessary since your third range is bigger than the first two). In article , Pete Elbert wrote: I am trying to calculate a total based on the number of items a person purchases that fall within one of these three amounts (1-9,10-19,20-30). Can anyone help |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
total amount based on selections of 1-9, 10-19 and 20-30
Thanks once again. I really appreciate the help. That did the trick. Take
care "JE McGimpsey" wrote: One way: =IF(A1=0,"",CHOOSE(...)) In article , Pete Elbert wrote: Using this formula makes it work just the way I want it to. One issue that I am not sure how to add into the formula is when cell A1 is empty or has a zero in it, I would like the cell that provides the total not to show anything or just a zero value. Otherwise the formula is just what I was looking for. Any solutions? "JE McGimpsey" wrote: One way (though your problem statement is rather vague): =CHOOSE(INT(A1/10)+1,"Total 1", "Total 2", "Total 3", "Total 3") (the last "Total 3" is necessary since your third range is bigger than the first two). In article , Pete Elbert wrote: I am trying to calculate a total based on the number of items a person purchases that fall within one of these three amounts (1-9,10-19,20-30). Can anyone help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| need a formula to get the total $ amount | Excel Discussion (Misc queries) | |||
| How can I calculate amount of time left based on amount spent? | Excel Worksheet Functions | |||
| Top Five selections based on sum of items meeting criteria | Excel Worksheet Functions | |||
| having a set $ amount be deducted from any given total | Excel Discussion (Misc queries) | |||
| How do I calculate Amount of Sales Tax from Total Amount? | Excel Worksheet Functions | |||
Linear Mode
