excel help

  casper69 07:35 03 May 2007
Locked

hello all

i know how to create a graph with formulas at the bottem to calculate totals, but is there a way to turn the numbers in to text and it still adds up a total i.e 4 x car text in a coloum and then the total will come out as number 4

cheers

  casper69 08:45 03 May 2007

also if the coloumn had 3 different texts i.e. car, van and bike can i make a formula to add up all of them in there own total

cheers

  skeletal 12:01 03 May 2007

If I have understood you correctly, I suggest you investigate the use of string manipulation e.g. Assume you type “5 bikes” in cell A1. In cell B1 type “=VALUE(LEFT(A1,1))” without the inverted commas. The number 5 will appear in cell B1.

LEFT(A1,1) extracts the first character in the string in A1 (i.e. the part that looks like the number 5).
VALUE, then takes that string, and turns it into the number 5.

The problem is this will only work for number between 0 and 9.

If you do LEFT(A1,2), you get the first two characters, but if you then put in a string “5bikes”, you get “5b” which will generate an error. “5 bikes” is OK, because of the space between 5 and bikes.

If you are careful with just how you enter the strings, this simple approach may work.

Skeletal

  VoG II 12:10 03 May 2007

If I understand the question, with names in A1:A10

=COUNTIF(A1:A10,"car")

will count the occurrences of car.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

Add Depth Of Field to a photo using Tilt Shift Blur in Photoshop

iPhone tips & tricks

Les meilleures tablettes 2017