Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-06-2011, 01:09 AM
KIM SOLIS KIM SOLIS is offline How to count the frequency of data and also tally value from an array of Excel record Windows XP How to count the frequency of data and also tally value from an array of Excel record Office 97
Novice
How to count the frequency of data and also tally value from an array of Excel record
 
Join Date: Sep 2011
Posts: 29
KIM SOLIS is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 09-06-2011, 02:06 AM
OTPM OTPM is offline How to count the frequency of data and also tally value from an array of Excel record Windows 7 32bit How to count the frequency of data and also tally value from an array of Excel record Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi
Please see attached solution.
Hope this helps.
Tony(OTPM)
Attached Files
File Type: xlsx Grades_Fees Calculation.xlsx (10.4 KB, 24 views)
Reply With Quote
  #3  
Old 09-06-2011, 11:55 AM
KIM SOLIS KIM SOLIS is offline How to count the frequency of data and also tally value from an array of Excel record Windows XP How to count the frequency of data and also tally value from an array of Excel record Office 97
Novice
How to count the frequency of data and also tally value from an array of Excel record
 
Join Date: Sep 2011
Posts: 29
KIM SOLIS is on a distinguished road
Default

Hi Tony,

Wow! You got it perfectly right! Thanks a lot.

Kim
Reply With Quote
  #4  
Old 09-06-2011, 12:25 PM
KIM SOLIS KIM SOLIS is offline How to count the frequency of data and also tally value from an array of Excel record Windows XP How to count the frequency of data and also tally value from an array of Excel record Office 97
Novice
How to count the frequency of data and also tally value from an array of Excel record
 
Join Date: Sep 2011
Posts: 29
KIM SOLIS is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 09-07-2011, 07:57 AM
OTPM OTPM is offline How to count the frequency of data and also tally value from an array of Excel record Windows 7 32bit How to count the frequency of data and also tally value from an array of Excel record Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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)
Reply With Quote
  #6  
Old 09-07-2011, 09:01 AM
KIM SOLIS KIM SOLIS is offline How to count the frequency of data and also tally value from an array of Excel record Windows XP How to count the frequency of data and also tally value from an array of Excel record Office 97
Novice
How to count the frequency of data and also tally value from an array of Excel record
 
Join Date: Sep 2011
Posts: 29
KIM SOLIS is on a distinguished road
Default

Thanks Tony, So I got it loud and clear... and finally .
Reply With Quote
Reply

Thread Tools
Display Modes


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 frequency of data and also tally value from an array of Excel record How to count the total cells that contains text data? Learner7 Excel 1 06-30-2011 04:13 PM
How to count the frequency of data and also tally value from an array of Excel record 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

Other Forums: Access Forums

All times are GMT -7. The time now is 09:13 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft