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

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

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

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

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

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

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

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