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!

Assuming student Ids are in A1:A999
=COUNTIF(A1:A999,"1*")
=COUNTIF(A1:A999,"2*")
=COUNTIF(A1:A999,"3*")

HTH
--
AP

a écrit dans le message de news:
...
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!

For free

=SUMPRODUCT(--(TRIM(LEFT(A2:A300,1))="1"))

replace "1" with "2" and "3" for the other

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


wrote in message
oups.com...
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!

Hi, Peo

Why do use TRIM on a 1-character string?

Cheers,
--
AP

"Peo Sjoblom" a écrit dans le message
de news: ...
For free

=SUMPRODUCT(--(TRIM(LEFT(A2:A300,1))="1"))

replace "1" with "2" and "3" for the other

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


wrote in message
oups.com...
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!

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.

--

Peo

"Ardus Petus" wrote in message
...
Hi, Peo

Why do use TRIM on a 1-character string?

Cheers,
--
AP

"Peo Sjoblom" a écrit dans le message
de news: ...
For free

=SUMPRODUCT(--(TRIM(LEFT(A2:A300,1))="1"))

replace "1" with "2" and "3" for the other

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


wrote in message
oups.com...
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!

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! :)


Peo Sjoblom wrote:
For free

=SUMPRODUCT(--(TRIM(LEFT(A2:A300,1))="1"))

replace "1" with "2" and "3" for the other

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


wrote in message
oups.com...
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!