Help with formula to sum totals for unit prices times number of units

[Steve P]

Hi,

I am having problems coming up with a solution to this: I have two columns, one with a list of unit prices and one with an x,2x,3x,etc (where "x" is to denote # of units). I am trying to avoid using just a number for # of units, although I realize a simple sumproduct(a:a,b:b) would probably work.

I am stuck because I cannot figure out how to create the formula to correctly sum the total price (units prices * number of corresponding units) WITHOUT using an actual number for the # of units. If you can't already tell, I am trying to build a budget template where a user will use a drop-down (data validation) to select the number of units, and the total cost will be a sum at the bottom of the column. For a better visual, please see below:


Column A Column B

$49 x

$25 2x

$100 x

$325 3x


total cost =WHAT FORMULA to get ((49*1)+(25*2)+ ect...)???


Please help!!


Thanks!

 

[kchiba]

Hi Steve,


You can use a helper column with the following formula :


This will extract the figures before the x and convert it to a number


=IF(FIND("x",C2,1)=1,1,MID(C2,1,FIND("x",C2,1)-1))

You can the use the Sumproduct with the helper column


cheers


kanti

 

[kchiba]

Hi Steve,


I used the worng columns the formula with your columns would be in Column C as follows:


=--IF(FIND("x",B2,1)=1,1,MID(B2,1,FIND("x",B2,1)-1))


note the double negative signd before the IF


Cheers


kanti

 

[Hui]

Steve

I would go with the numbers without the X's, which will mean you can use a simple Sumproduct formula to get your answer.


You can then use a Custom Number format like # x, #x or #X etc

which will display the numbers as you want them

 

[cyrilz]

Custom number formats are better and a faster way to achieve this, since if you achieve to have a high number of row, Excel will spent all its time calculating the helper column ...


Note that they can print too

 

[keymaster]

ok... as Cyril or Hui suggest use actual numbers instead of xs. If that doesnt work, try this formula.


=SUMPRODUCT(A1:A10,VALUE(IF(B1:B10="x","1",SUBSTITUTE(B1:B10,"x",""))))


you must press CTRL+SHIFT+ENTER after this.


If somehow you can get 1x instead of just x, then this non-array formula works too,


=SUMPRODUCT(A1:A10,VALUE(SUBSTITUTE(B1:B10,"x","")))

 

[Steve P]

Thanks to all!!! You guys are great!