Thread: Assigning a # value to a letter in Excel 2007

Countif sounds like the way to go.

Click on the little fx next to the formula entry bar at the top left of the screen and select countif. This will then show a dialog box on the required arguments.

Yes, as previously stated, COUNTIF will work

Assuming your data is in column A, rows 1 to 10, this is what you'd have to enter to count the no. of instances of the letter R. Put this formula elsewhere in the spreadsheet.

=COUNTIF(A1:A10,"r")


You'll need one instance of the formula for each letter to be counted.

Hey Mick,

Just call your 'helpdesk' if you need help

Speak Soon.

Originally Posted by MickG

I really need to do an excle course but until then, can anyone talk me through how I assign different number values to letters in a colum or row. I think I need to use a "countif" formula.

Essentially I am recording customer sources of business approx 50 entries p/day and will have approx 10-15 sources of business which I intended to assign an individual letter for (example: newspaper ad = N or n, radio ad = R or r) and then at the end of each day, I want another box on my spreadsheet to show me how many "N"s or "R"s i have had for the day and so on and so forth. I can then graph the totals boxes easily.

Thanks in advance, Mick

Originally Posted by stikman

Hey Mick,

Just call your 'helpdesk' if you need help

Speak Soon.

Yeah was going to but you'll start charging me soon Both foz.in.oz and P38arover have sorted me out.....wish I knew about this gem a few spreadsheets back I really should do a course soon but hey, AULRO always comes through with the goods when you need it.

Thanks again guys........rest assured there will be plenty more Excel questions to come in the future

Aye, Mick

Originally Posted by p38arover

Yes, as previously stated, COUNTIF will work

Assuming your data is in column A, rows 1 to 10, this is what you'd have to enter to count the no. of instances of the letter R. Put this formula elsewhere in the spreadsheet.

=COUNTIF(A1:A10,"r")


You'll need one instance of the formula for each letter to be counted.

Change the formula of Ron's to read

=COUNTIF(UPPER(A1:A10),"R")

Should work and translates all letters to uppercase. I haven't got Excel at home any more so can't test it.

The data doesn't need conversion to uppercase for the function to work. I tested it.

=COUNTIF(A1:A10,"r") works with upper or lower case data.

Regrettably, COUNTIF(UPPER(A1:A10),"R") doesn't work. I suspect it's because you can't use the UPPER function on a range.

Originally Posted by p38arover

The data doesn't need conversion to uppercase for the function to work. I tested it.

=COUNTIF(A1:A10,"r") works with upper or lower case data.

Regrettably, COUNTIF(UPPER(A1:A10),"R") doesn't work. I suspect it's because you can't use the UPPER function on a range.

Thanks Ron. I think I must have known this, but like I said, couldn't test it. Did realise COUNTIF wasn't case dependant.

Thanks again guys, yes tested with upper and lower case and formula works perfectly.

Appreciate everyone's time and input, Mick