Excel Formulas

  • Thread starter Thread starter Royal Preston
  • Start date Start date
R

Royal Preston

I have generated a list of products in in one column. I
also have a quanity column. Is there any way to
associate a particular cost for each item in the list and
associate it with the quanity column to give a total in
another column. I have been trying to to do a SUMIF
(with text value) function but cant seem to make it
work....Help pleae
 
A1 - Name of product <keyed in>
B1 - Quantity on hand <keyed in>
C1 - Enter unit price (cost?, value?)<Format to currency><keyed in>
Enter this formula in D1:
=B1*C1
And <Format to currency>

Now, select D1.
Place the cursor over the lower right comer of the selected cell,
until it changes from a fat white cross to a skinny black cross.
Then click, and drag down as far as needed to copy this formula down the
column.


I have generated a list of products in in one column. I
also have a quanity column. Is there any way to
associate a particular cost for each item in the list and
associate it with the quanity column to give a total in
another column. I have been trying to to do a SUMIF
(with text value) function but cant seem to make it
work....Help pleae
 
I can do what you explained but without generating
another column is there anyway with the list in A1 with
all the products is there anyway in C1 to make a formula
without making another column for cost but to have the
costs in the formula and put this amount in C1 based on
the quanities in B2.
 
If I understand what you're asking, doesn't that mean that the products will
all have the same price?

For example, if you enter this formula in C1:

=B1*$Z$1
And copy this down,
Where you enter an amount in cell Z1,
All products will have whatever price you entered in cell Z1!

I don't believe that you wish to hardcode a price into each individual cell
in column C ... do you?

=B1*2.25
=B2*1.08
=B3*4.45
....etc.

That would seem ridiculous to me.

If this doesn't help, post back with a more extensive explanation.
--

Regards,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I can do what you explained but without generating
another column is there anyway with the list in A1 with
all the products is there anyway in C1 to make a formula
without making another column for cost but to have the
costs in the formula and put this amount in C1 based on
the quanities in B2.
 
If you have your prices in column 2 of a range named "PriceList", the
following formula would give the total sales for apples:

=SUMIF(A2:A10,"apples",B2:B10)*VLOOKUP("apples",PriceList,2,0)
 
Thanks for your help..Let me try and explain..A1 is set
up with a drop down list of products...B1 is
quanity...now for C1..I am trying to make a formula ie:
IF A2 is T-shirts then multiply B2 times $5 if I select
Mugs in A1 then C1 is B1 times $10. I cant put a
variable in C1 to allow the different costs based on what
I select in A1...

Thanks again for the help and hope this makes more sense.
 
For that you need to create a data list of existing prices.

Since you already have an existing list that you use for your drop down
menu, just add a column next to it, with the corresponding prices.

For example, if your product drop down list was in J1:J25, then place the
matching prices in K1:K25.

Then you could try this formula in C1:

=IF(A1<>"",B1*INDEX($K$1:$K$25,MATCH(A1,$J$1:$J$25,0)),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Thanks for your help..Let me try and explain..A1 is set
up with a drop down list of products...B1 is
quanity...now for C1..I am trying to make a formula ie:
IF A2 is T-shirts then multiply B2 times $5 if I select
Mugs in A1 then C1 is B1 times $10. I cant put a
variable in C1 to allow the different costs based on what
I select in A1...

Thanks again for the help and hope this makes more sense.
 
Back
Top