+ Reply to Thread
Results 1 to 6 of 6

Require Help creating two formulae (3 vertical criteria 1 horizontal)

  1. #1
    Registered User
    Join Date
    08-24-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    6

    Require Help creating two formulae (3 vertical criteria 1 horizontal)

    Back again hoping someone can help.

    I had a data set that I need to summarize and could use some help with a formula to help pick out and sum data based on certain criteria.

    I have attached an example below to help you understand the formula I need help to create.

    The difficulty I am having is there are 3 Identifying criteria that the data is sorted by vertically and the whole data set is split monthly horizontally.

    I need help with two formula.

    1) A formula that pulls through the data based on the 3 criteria and a month specified.

    2) A formula that sums the information over a period of time between the two summarized dates.

    I really dont want to resort to Pivot Tables if it can be helped.

    If anyone can help it would be much appreciated.

    Many Thanks in advance.
    Attached Files Attached Files
    Last edited by Narrov; 09-08-2015 at 11:43 AM.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Require Help creating two formulae (3 vertical criteria 1 horizontal)

    Where are these formulas to go and what are your expected results?

    BSB

  3. #3
    Registered User
    Join Date
    08-24-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    6

    Re: Require Help creating two formulae (3 vertical criteria 1 horizontal)

    BSB,

    Apologies this wasnt clear. I've attached a fresh document. The areas highlighted in Green are where the formulas should go.

    Thanks

    Narrov
    Attached Files Attached Files

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Require Help creating two formulae (3 vertical criteria 1 horizontal)

    You can do this using SUMPRODUCT.

    See attached.

    BSB
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-24-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    6

    Re: Require Help creating two formulae (3 vertical criteria 1 horizontal)

    BSB

    Once again you're an Excel God!

    I had no idea that Sumproduct did this. The information I found on the internet regarding sumproduct had nothing in them about this functionality.

    Thanks

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Require Help creating two formulae (3 vertical criteria 1 horizontal)

    SUMPRODUCT is clunky and inefficient on really big datasets, but it does the job here just fine.

    Happy to help

    BSB

+ 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. Sum Multiple Criteria horizontal and vertical
    By baronk in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-16-2015, 07:55 AM
  2. [SOLVED] 3 criteria for horizontal and vertical matching
    By mator in forum Excel General
    Replies: 5
    Last Post: 12-24-2014, 07:19 AM
  3. 3 criteria for horizontal and vertical matching from a table
    By mator in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-24-2014, 06:28 AM
  4. [SOLVED] Lookup with vertical and horizontal criteria
    By busygurl in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-23-2014, 12:03 AM
  5. Replies: 3
    Last Post: 07-17-2014, 07:43 PM
  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: 1
    Last Post: 07-29-2008, 10:50 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