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

1. 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.``

2. 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

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

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. Re: Sumproduct with Multiple (Header) Criteria - Sumproduct, Index, Match

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

5. 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.

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

Originally Posted by NBVC
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!!!

Users Browsing this Thread

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

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