+ Reply to Thread
Results 1 to 6 of 6

Sumproduct with Multiple (Header) Criteria - Sumproduct, Index, Match

  1. #1
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    895

    Sumproduct with Multiple (Header) Criteria - Sumproduct, Index, Match

    Hello Excel Gurus,

    I have a simple question for you (hard for me). I believe it involves sumproduct, index and match. Of course, if there's a simpler way, would be better.

    The problem is this. I have a list of countries (going down the rows) with it's respective gross revenue and net revenues for each month (across the column).

    I'm trying to find the sum of each country in each month for each revenue.

    I have attached an excel file for a clear picture of how the data was set up and what are my desired results to be.

    I googled sumproduct, and came up with this (see below - this is only for country Asia for Net Revenue in Jan 2011). However, this is "manually" looking at the headers. Therefore, I'm wondering if there's a way to make it "auto" to be able to smart enough to look at the headers (revenue and its month) and sum it up through formulas (which I believe it can be done)



    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by dluhut; 04-19-2012 at 12:45 PM.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Sumproduct with Multiple (Header) Criteria - Sumproduct, Index, Match

    For your example you would be better served by SUMIF, i.e.

    =SUMIF($A$3:$A$11,$A21,D$3:D$11)

    If you have data in columns D to Z then you can make that pick a specific column based on a date in row 1 by this method

    =SUMIF($A$3:$A$11,$A21,INDEX(D$3:Z$11,0,MATCH($B21,D$1:Z$1,0)))

    where $B21 contains your date
    Audere est facere

  3. #3
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    895

    Re: Sumproduct with Multiple (Header) Criteria - Sumproduct, Index, Match

    Thanks for replying daddylonglegs,

    From the 2nd formula that you've wrote, isn't it still "manually" looking at the headers.

    Reason being, the match ($B21, D$1:Z$1,0) only looks at the month, but it doesn't look at the respective revenue's column.

    i.e. Jan 2011 has 2 columns. In column B (Gross Revenue) and column D (Net Revenue).

  4. #4
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    895

    Re: Sumproduct with Multiple (Header) Criteria - Sumproduct, Index, Match

    Any Excel Gurus and Experts that can help me out? Is it doable?

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Sumproduct with Multiple (Header) Criteria - Sumproduct, Index, Match

    Merged cells are not your friends....

    Having said that....

    in B21:

    Please Login or Register  to view this content.
    copied across matrix.

    in M21:

    Please Login or Register  to view this content.
    copied across matrix.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  6. #6
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    895

    Re: Sumproduct with Multiple (Header) Criteria - Sumproduct, Index, Match

    Quote Originally Posted by NBVC View Post
    Merged cells are not your friends....

    Having said that....

    in B21:

    Please Login or Register  to view this content.
    copied across matrix.

    in M21:

    Please Login or Register  to view this content.
    copied across matrix.
    It works PERFECTLY!!!

    Thanks!

    I'll mark this solved and please check your reputation, as I've increased it.

    Thanks Once again!!! You make my work life easier!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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