MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Making a String equal to a Number


Posted by Jack on August 26, 2001 8:19 PM

I'm relatively new at excel and I can't for the life of me find this function. I want to bind a number to text, for example:

"Deliverable Goods" = $250

Now I want to be able to type in Deliverable Goods and have that string appear, but at the bottom of the screen I want to have a total of the Deliverable Goods on that page. So say I had 6 Deliverable Goods on that page, I want a Total= $1500.

Any help would be greatly appreciated, I'm about to pull out my hair's hair. I just can't find this function and I'm dead in the water without it (perhaps it doesn't exist but I'd find that extremely wierd).

In essance I want A to equal 5 (A=5) so then I can take the amount of "A"'s in a column (for example 7) and have it total 35 at the bottom of the page.


Posted by Gerry on August 26, 2001 9:24 PM


If you want the number of "A"'s in Column A multiplied by 5 :-
=COUNTIF(A:A,"A")*5


Posted by Gerry on August 26, 2001 9:33 PM

Also ...


If you often need to change the text to be totaled and/or the value to multiply by, you can put the text and value in different cells from the formula and then refer to those cells in the formula. For example, put A in B1, 5 in C1, and this formula in D1 :-
=COUNTIF(A:A,BI)*C1

Posted by Jack on August 26, 2001 9:37 PM

Ok, here's how I got it to work. Remember I'm not that great at excel so there is probably a much easier better way.

I made 3 hidden columns.

column 1, changed the string to a number =len(e5) in my case it equaled 10

column 2, true or false....=IF(10=F5,1350,300) so if f5 equaled 10 it would give me the value i wanted 1350, if not it would give me 300

column 3, another true false cause if there was no value it would give me pesky 0's/300's (if there was no string it len would give me a value of 300 cause 0 also doesn't equal 10 from column 2) =IF((F5=0),0,G5) that got rid of em. And I can total this hidden column.

TaDa, Badda boom.

Posted by Jack on August 27, 2001 1:34 AM

Re: Also ...

Hey, thanks, that's perfect. I appreciate the help, you saved me tons of extra work.

Thanks again,
Jack