+ Reply to Thread
Results 1 to 4 of 4

Summarize Data by Month by Person

  1. #1
    Registered User
    Join Date
    06-30-2016
    Location
    Cleveland, Ohio
    MS-Off Ver
    2016
    Posts
    2

    Summarize Data by Month by Person

    I have a table of employees and hours worked i'm trying to summarize in a dashboard on a separate sheet. I'm trying to write a formula that makes adding new employees to the table and dashboard easy.
    Here is a screenshot of the data table:
    data_table.png

    Here is a sample of what the dashboard would look like.
    dashboard.png

    I would like to formula to use the name in column A as a match for the datatable,and aggregate the weeks into months. As new people will be being added and removed, i'm trying to make it simple add new people the list. I have about 40 people I'm tracking hourly data for at present, with them changing every month.
    Attached Files Attached Files
    Last edited by mason736; 07-27-2016 at 04:29 PM. Reason: Added file

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,672

    Re: Summarize Data by Month by Person

    Please post a sample file (not image).

    To upload a file, click "Go Advanced" then scroll down to "Manage Attachments"

  3. #3
    Registered User
    Join Date
    06-30-2016
    Location
    Cleveland, Ohio
    MS-Off Ver
    2016
    Posts
    2

    Re: Summarize Data by Month by Person

    I added the file, thanks!

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,672

    Re: Summarize Data by Month by Person

    In B2 of "Dashboard"

    =SUMPRODUCT((Datatable!$A$3:$A$7=dashboard!$A2)*(Datatable!$B$1:$M$1=dashboard!B$1)*(Datatable!$B$3:$M$7))

    Copy across and down

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help getting averages for each person for each month
    By ksquires in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-01-2016, 04:42 AM
  2. Check for value on a by person by month basis.
    By MRHF in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-12-2015, 01:58 PM
  3. Summarize total amount with different month
    By umininnel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-27-2015, 08:28 PM
  4. Calculating How Much Each Person Brings in a Month
    By Big D in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-17-2013, 11:34 AM
  5. Replies: 6
    Last Post: 03-03-2013, 01:41 AM
  6. Best way to summarize into month
    By bluerog in forum Excel General
    Replies: 4
    Last Post: 02-22-2011, 05:21 PM
  7. Summarize numbers by month
    By hereami in forum Excel General
    Replies: 3
    Last Post: 05-17-2010, 02:21 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1