excel - SUMIF using named array consisting of non-adjacent columns

Question

Good morning!

I am trying to create a dynamic SUMIF by using a named array containing data in non-adjancent columns.

The situation is as follows:

1. I have a file with monthly data, where the columns are labelled as:

Jan 2010, Feb 2010, (...), Dec 2010, FY 2010 (...) Jan 2020, Feb 2020, (...), Dec 2020, FY 2020

2. I want to sum only the monthly data (Jan 2010, Feb 2010... Dec 2010, Jan 2011, Feb 2011, etc) and exclude the data in the FY column (FY 2010, FY 2011, etc etc)

3. I have named the array of monthly data that interests me as a range ("relevant_monthly_data")

4. I am looking at individual rows of data that interest me ("country_range"). Note that the country_range consists of only one column with all the countries. It has the same number of rows as "relevant_monthly_data".

5. The formula that I am using only works for the first set of adjacent columns (Jan-Dec 2010), but doesn't capture Jan-Dec 2011, 2012... all the way through to 2020 even though they are all part of the named array).

=SUM(INDEX(relevant_monthly_data,MATCH("Guatemala",country_range,0),))

Could you please help me?

I am sure it is a simple question, but I somehow cannot figure out how to sum this range beyond 2010 monthly entries...

Thank you in advance for your help!

Answer 1

等待大神答复