SUMMING TOTALS BASING ON CHARATERS ENTERED IN CELLS OF ONE COLUMN
Hi Team,
I need your help on this. i would like to get the total of the characters in column D (TP,CS PS,BTP) and let the total of each characters placed under their respective type in column E,F,G,H) as below accordingly. I also need column (E,F,G,H) to update their total automatically whenever a new value is entered in column D with the same specified charaters as above. Below is only a sample of what i am working on but the data is quite large. I will be grateful if any of you could assist me with this excel work ASAP For further information, i can send you a detailed screen shot directly to your email A B C D E F G H DATE ID NO. NAME TYPE TP PS CS BTP 20.05.2011 125458 otim one TP 20.05.2011 123568 john b TP 20.05.2011 123568 okello PS 20.05.2011 123568 peter TP 20.05.2011 123568 han PS 20.05.2011 123568 hellen CS 20.05.2011 123568 simon BTP 20.05.2011 123568 walter CS 20.05.2011 123568 hon PS 20.05.2011 123568 gred PS 20.05.2011 123568 boyz TP 20.05.2011 123568 willo CS 20.05.2011 123568 sp BTP Rgds Lawsim SP |
SUMMING TOTALS BASING ON CHARATERS ENTERED IN CELLS OF ONE COLUMN
This calls for the SUMIF function.
If you want the total of each type in row 2 at the top under each TYPE column heading, begin your data table at A3 In E2, place =SUMIF($D:$D, E$1,$B:$B). Drag this formula across from E through H. The E$1 will adjust automatically to cover the other TYPE column headings. Pete On May 23, 6:54*am, Lawsim wrote: Hi Team, I need your help on this. i would like to get the total of the characters in column D (TP,CS PS,BTP) and let the total of each characters placed under their respective type in column E,F,G,H) as below accordingly. I also need column (E,F,G,H) to update their total automatically whenever a new value is entered in column D with the same specified charaters as above. Below is only a sample of what i am working on but the data is quite large. I will be grateful if any of you could assist me with this excel work ASAP For further information, i can send you a detailed screen shot directly to your email A * * * B * * * C * * * D * * * E * * * F * * * G * * * H DATE * *ID NO. *NAME * *TYPE * *TP * * *PS * * *CS * * *BTP 20.05.2011 * * *125458 *otim one * * * *TP 20.05.2011 * * *123568 *john b *TP 20.05.2011 * * *123568 *okello *PS 20.05.2011 * * *123568 *peter * TP 20.05.2011 * * *123568 *han * * PS 20.05.2011 * * *123568 *hellen *CS 20.05.2011 * * *123568 *simon * BTP 20.05.2011 * * *123568 *walter *CS 20.05.2011 * * *123568 *hon * * PS 20.05.2011 * * *123568 *gred * *PS 20.05.2011 * * *123568 *boyz * *TP 20.05.2011 * * *123568 *willo * CS 20.05.2011 * * *123568 *sp * * *BTP Rgds Lawsim SP |
SUMMING TOTALS BASING ON CHARATERS ENTERED IN CELLS OF ONE COLUMN
In E2 enter =COUNTIF($D:$D,E1)
Copy across to H2. Gord Dibben MS Excel MVP On Mon, 23 May 2011 04:54:56 -0700 (PDT), Lawsim wrote: Hi Team, I need your help on this. i would like to get the total of the characters in column D (TP,CS PS,BTP) and let the total of each characters placed under their respective type in column E,F,G,H) as below accordingly. I also need column (E,F,G,H) to update their total automatically whenever a new value is entered in column D with the same specified charaters as above. Below is only a sample of what i am working on but the data is quite large. I will be grateful if any of you could assist me with this excel work ASAP For further information, i can send you a detailed screen shot directly to your email A B C D E F G H DATE ID NO. NAME TYPE TP PS CS BTP 20.05.2011 125458 otim one TP 20.05.2011 123568 john b TP 20.05.2011 123568 okello PS 20.05.2011 123568 peter TP 20.05.2011 123568 han PS 20.05.2011 123568 hellen CS 20.05.2011 123568 simon BTP 20.05.2011 123568 walter CS 20.05.2011 123568 hon PS 20.05.2011 123568 gred PS 20.05.2011 123568 boyz TP 20.05.2011 123568 willo CS 20.05.2011 123568 sp BTP Rgds Lawsim SP |
All times are GMT +1. The time now is 11:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com