|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to count the frequency of data and also tally value from an array of Excel record
Hi all,
I have this Excel data shown in three columns. per Table1, below: Name................ Grade Level................... Fees Leo ..................... Gr-5 ......................1,200.00 May .....................Gr-6 ......................5,000.00 Jed ......................Gr-5 ........................800.00 Ana .....................Gr-5 ........................100.00 Wil ......................Gr-4 ........................300.00 ...and so on (I have about 800 rows of similar data to tally) What I want to do is to generate an Excel Report, per Table2 below, that counts the frequency in the Grade Level column and adds up Fees by Grade Level, as in: Total Students........... Grade Level ............Total Fees 1 .............................Gr-4 .....................300.00 1 ............................ Gr-6 ...................5,000.00 3............................. Gr-5 ...................2,100.00 The output i need is that the collated data should display in only one row as displayed above. Both reports must be in one worksheet, with Table1 in sheet1 and Table2 in sheet2. As much as possible my Table 2 output should automatically display (after setting some excel formula as needed). Is there a way to do this simply and quickly in Excel. Thanks for any advice. Kim S. Last edited by KIM SOLIS; 09-06-2011 at 01:13 AM. Reason: does not align |
#2
|
|||
|
|||
Hi
Please see attached solution. Hope this helps. Tony(OTPM) |
#3
|
|||
|
|||
Hi Tony,
Wow! You got it perfectly right! Thanks a lot. Kim |
#4
|
|||
|
|||
PS/ By the way Tony, how would I reflect the desired Table (Total Students / Grade Level /Total Fees) in sheet2 of my worksheet by linking it directly to the sheet1 data?
Kim |
#5
|
|||
|
|||
Hi Kim
Simply cut and paste the table to Sheet 2 and modify each formula from: =SUMIF(B:B,"Gr-4",C:C) to =SUMIF(Sheet1!B:B,"Gr-4",C:C) Good luck. Tony(OTPM) |
#6
|
|||
|
|||
Thanks Tony, So I got it loud and clear... and finally .
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA code to update record in Access 2003 using Userform in Excel | primmer3001 | Excel Programming | 0 | 08-29-2011 04:25 PM |
How to count the total cells that contains text data? | Learner7 | Excel | 1 | 06-30-2011 04:13 PM |
Count in Excel | zanat0s | Excel | 3 | 06-09-2011 10:53 AM |
Look up an array based on user input | johnsmb | Excel | 2 | 01-07-2011 01:12 PM |
How to count cells containing data and meet certain criteria | AdamNT | Excel | 1 | 08-11-2006 11:51 PM |