+ Reply to Thread
Results 1 to 4 of 4

SumIfs / Sumproduct for table with vertical and horizontal lookup criteria (text+dates)

  1. #1
    Registered User
    Join Date
    06-06-2012
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    SumIfs / Sumproduct for table with vertical and horizontal lookup criteria (text+dates)

    Hi,

    I've tried searching other threads and post for similar problem and applied various solutions without any luck.

    I have 2 sheets within a document, 1 sheet (data_inputs) contains a table of data across a calendar year by weeks.
    On the 2nd sheet (monthly) I want to summarise specific values by month.

    I've tried a hybrid of sumifs and stumble across sumproduct but can't get my sheet to work.

    The monthly table needs to calculate the number of 'things' that were completed & recorded each week within the given monthly period.
    The labels along the horizontal fields represent the text lookup values. As the weekly period starts and finishes from Saturday to Friday, I've added in a reference row on the data_inputs tab to reflect what monthly reporting period the values need to be added into on the monthly sheet. I've also add in the 1st and end of each month to try set the range.

    See attached document for reference.

    Any help is appreciated.

    Thanks
    Ben
    Attached Files Attached Files
    Last edited by benvass; 09-17-2018 at 07:36 AM. Reason: Problem has now been solved.

  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,726

    Re: SumIfs / Sumproduct for table with vertical and horizontal lookup criteria (text+dates

    Try this in B5 of the Monthly sheet:

    =SUMPRODUCT((Data_Inputs!$F$4:$BF$223)*(Data_Inputs!$F$1:$BF$1>=$AU5)*(Data_Inputs!$F$1:$BF$1<=$AV5)*(Data_Inputs!$E$4:$E$223=B$4))

    then copy across and down as required.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    06-06-2012
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: SumIfs / Sumproduct for table with vertical and horizontal lookup criteria (text+dates

    Hi Pete_UK,

    Works a treat, thank you very much and for the quick response.

    Thanks
    Ben

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

    Re: SumIfs / Sumproduct for table with vertical and horizontal lookup criteria (text+dates

    You're welcome - thanks for the rep.

    Pete

+ 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. Sumproduct with criteria horizontal and vertical and > than
    By krunk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-09-2017, 11:37 AM
  2. [SOLVED] SUMIFS(?) I need to sum data using both horizontal and vertical criteria
    By bighandsam in forum Excel General
    Replies: 22
    Last Post: 01-06-2017, 11:31 PM
  3. Replies: 5
    Last Post: 10-03-2016, 03:39 AM
  4. solved
    By ravidesai in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-18-2016, 10:55 AM
  5. [SOLVED] Lookup with vertical and horizontal criteria
    By busygurl in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-23-2014, 12:03 AM
  6. Lookup Table Using Horizontal and Vertical Criteria
    By bbmonkey87 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2014, 06:31 PM
  7. Replies: 5
    Last Post: 06-06-2013, 05:12 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