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.

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

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.