|
Get first character in cell and count all cells that begin with 1,2,3 etc
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! |
Get first character in cell and count all cells that begin with 1,2,3 etc
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! |
Get first character in cell and count all cells that begin with 1,2,3 etc
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! |
Get first character in cell and count all cells that begin with 1,2,3 etc
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! |
Get first character in cell and count all cells that begin with 1,2,3 etc
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! |
| All times are GMT +1. The time now is 10:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com