Get first character in cell and count all cells that begin with 1,2,3 etc

[keith.j.parker]

Hi,

I work at an elementary school and we keep track of student cafeteria
activities through excel. We have students in three categories:

1 - FREE
2 - REDUCED
3 - PAID

Each student's ID begins with a letter that corresponds to their
status.

Our spreadsheet has a colum of student ID's and we would like to
automate how many free, reduced an paid breakfasts and lunches we serve
each day.

The logic would go something like this:

1) Take the leftmost character of the cell
2) If it begins with a 1 - add it to the count of FREE lunches served
3) If it begins with a 2 - add it to the count of REDUCED lunches
served
4) If it begins with a 3 - add it to the count of PAID lunches served
5) Move on to the next cell down and do the same

I hope this makes sense. I'm not an excel person.

We currently have 3 seperate cells where FREE, REDUCED and PAID totals
are manually calculated and entered.

Would it be posible to insert the "FREE" formula in the FREE total
cell, etc.?

Any help would be appreciated - please fogive if the question is not
clear.

Thanks!

 

[Peo Sjoblom]

Hi Ardus,

Just a precaution in case there would be a leading space, 9 out of 10 it
wouldn't be necessary but it is a habit. If one enters numbers with a
leading space it won't have any affect since Excel will figure out it is a
number and dump the space. If one enters a text string with a leading space
Excel won't dump it.

 

[keith.j.parker]

Thank you all!

Actually, I wasn't very clear about the ID. It is a 9 digit number
that *begins* with a 1, 2, or 3. So the below should work - right? It
looks like the (LEFT(A2:A300), 1) will snag the leftmost character -
which is what I need. The TRIM is just gravy - no?

Thank you so much for this - it just tested it and it works
wonderfully.

I've shown your posts to the person I'm doing this for, just so she'll
know who to be grateful to!