+ Reply to Thread
Results 1 to 4 of 4

Sum weekly total of (*) at end of month for Company(*)

  1. #1
    Registered User
    Join Date
    02-04-2019
    Location
    Australia
    MS-Off Ver
    MSOffice 2010
    Posts
    25

    Sum weekly total of (*) at end of month for Company(*)

    Hi Everyone,

    Need a little help in trying to get the correct formula for this data-set. currently I have =LOOKUP(2,1/(Sheet2!$A$3:$A$250=$A2)/(Sheet2!$B$3:$E$250=B$2)) though it is not the correct total I am looking for..

    So.. Sheet two is a weekly total of issues each company has ( e.g. B3 to E3 )and I would like the total for those weeks (the full month e.g. B3+C3+D3+E3 = Total) to show up on Sheet 1 B2 based on the company name. and repeat for the each month after that.

    Thankyou!
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Sum weekly total of (*) at end of month for Company(*)

    You have a single space at the end of each of your labels in B1:D1 of the Data Register sheet, so you need to get rid of those, and then you can use this formula in B2:

    =SUM(INDEX('Data-set'!$B$3:$M$8,MATCH($A2,'Data-set'!$A$3:$A$8,0),MATCH(B$1,'Data-set'!$B$1:$M$1,0)):INDEX('Data-set'!$B$3:$M$8,MATCH($A2,'Data-set'!$A$3:$A$8,0),MATCH(B$1,'Data-set'!$B$1:$M$1,0)+3))

    Copy this across and down, as required.

    Hope this helps.

    Pete

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sum weekly total of (*) at end of month for Company(*)

    Hi petsean,

    I did this problem using the Power Query tool of Unpivot and then used the new table in a Pivot. No formulas needed.

    Unpivot using PQ then Pivot Table.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    02-04-2019
    Location
    Australia
    MS-Off Ver
    MSOffice 2010
    Posts
    25

    Re: Sum weekly total of (*) at end of month for Company(*)

    Thank you pete that worked perfectly and it is much appreciated

    as well thankyou marvin, that is actually a good idea to use pivot tables, not sure why I didn't think of it earlier!

+ 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. Pivot pie chart work % of total of company and total profit
    By faodavid in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-30-2018, 11:51 AM
  2. Replies: 5
    Last Post: 12-08-2016, 04:24 AM
  3. [SOLVED] Is there a way to insert all dates for a month weekly when user inputs Month and a Year?
    By uniqbboy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2014, 10:40 AM
  4. [SOLVED] Formula for weekly total, but not showing,until present month is entered.
    By paul*r in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-07-2014, 09:48 AM
  5. To find Weekly High and Low for the company
    By goodboy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-11-2014, 12:18 PM
  6. How to bring a weekly total into a monthly total
    By RBeng in forum Excel General
    Replies: 0
    Last Post: 03-09-2011, 02:42 PM
  7. [SOLVED] Graphing past months (totals) and current month (weekly total) in
    By Davin in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-27-2005, 12:05 PM

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