SUMIF, INDIRECT & INDEX/MATCH in one formula?

[Start123]

Hi Everyone

I have a very tough one here that I am struggling to solve. The back-story is:

Every month I receive a fresh report of 20k+ rows and over 50 columns. It is all contained on one worksheet. Each month, I add the most recent report to a new worksheet in a master file - the worksheets are named after the relevant month.

The foremost sheet on my master file is the dashboard, which uses data validation drop-down lists to pull through the correct data from the other sheets (named after the months):

-Drop-down list A = Month (Jan-Dec)
-Drop-down list B = Category (all with individual names, but for simplicity let's just call them A-G)
-Drop-down list C = Zone (geographical regions of the world: W Europe, N America and S America, let's say)

Changing any of these drop down lists pulls through the relevant results. List A (the months) is higher in the heirarchy than lists B or C, which both use A to get their results. There are three different results pulled through:

-Baseline value (an absolute value using a simple lookup using INDEX/MATCH)
-Last month value (calculated by dividing the sum of a particular column by the sum of another particular column)
-Current month value (calculated by dividing the sum of a particular column by the sum of another particular column)

The above three results are column headers in the reports for each month. The problem is, that the column that these column headers occupy changes every week (but not the rows that they occupy).

For the first data retrieval, which is just the overall total for the months, I am using the following formula:

=INDEX(INDIRECT("'"&$B$3&"'!2:2"),MATCH(B6,INDIRECT("'"&$B$3&"'!1:1"),0))

In this formula, B3 contains the drop-down list of the months, B6 contains 'Current Value' (which is a column header on the various worksheets), row 1:1 contains the various column headings (as there are many more than just the ones I am aiming to retrieve) and row 2:2 contains the sums of those columns (on the 'months' worksheets).

So, this formula is returning the 'Current Value' for any given month for all categories and all zones. This part is the part I am doing OK with. The problem arises when I try to retrieve the 'Current Value' for, let's say, 'June', 'Category B' and, say, N America.

This is where I need to try and introduce SUMIF into my formula. In very simple words, what I want to do is tell Excel to do the following:

1. Search on the worksheet that is named in 'drop-down list A' for the position of the column entitled 'X' (column X divided by Y forms the 'Current Value' column as above)
2. Sum up the values in column 'X' that match 'Category B'. Let's call this figure FI
3. Sum up the values in column 'Y' that match 'Category B'. Let's call this figure FII
4. Divide FI/FII to create the contribution of 'Category B' in June towards 'Current Value'

The complication arises because the column position of 'X' and 'Y' changes month to month, so I need to incorporate additional INDEX/MATCH functions as well as the SUMIF function.

Tough one, I know, but I'm hoping someone here might have a good idea for how to get 'round it.

Thanks very much

James

 

[Andrew Poulsom]

Which cell contains the heading of the last month column? Which cells contain the dropdowns for Category and Region? And in which columns are the Categories and Regions on your monthly sheets?

 

[Start123]

1. Heading of last month column - I am not entirely sure what you are referring to here. 'Last month' isn't a column heading. What my formula does (it has not been included above as it is not part of it, but I can include it if it would help?) is look up the month in the cell against a table of the months stored elsewhere, find the corresponding month number (i.e. May would be 5, June would be 6 etc) and minus 1 from this figure, then look up the result on THAT month's sheet.

2. Drop down for category is in E3 of the dashboard and for zone is in H3.

3. Categories and regions are in different columns each month - that is the problem I am trying to fix as, unfortunately, the formatting of the report I receive is not entirely consistent.

Thanks

 

[Start123]

I'll make a sample workbook that you can use to see what I am after

 

[Andrew Poulsom]

You said:

1. Search on the worksheet that is named in 'drop-down list A' for the position of the column entitled 'X' (column X divided by Y forms the 'Current Value' column as above)

Which cell contains 'X' and which cell contains 'Y', assuming you need to also search for that?

What is the heading of the Category column?

 

[Start123]

Do you know where/how I can upload a sample workbook? I just finished one then realised that we cannot post attachments. Thanks!

 

[Andrew Poulsom]

You can use a sharing site like Box.Net or you can just describe what you want to do in words.

 

[SLJones]

Hi,
Did you ever work this out? I'm looking to do exactly the same thing! I'm also struggling.

If you got it resolved then please share

Hi Everyone

I have a very tough one here that I am struggling to solve. The back-story is:

Every month I receive a fresh report of 20k+ rows and over 50 columns. It is all contained on one worksheet. Each month, I add the most recent report to a new worksheet in a master file - the worksheets are named after the relevant month.

The foremost sheet on my master file is the dashboard, which uses data validation drop-down lists to pull through the correct data from the other sheets (named after the months):

-Drop-down list A = Month (Jan-Dec)
-Drop-down list B = Category (all with individual names, but for simplicity let's just call them A-G)
-Drop-down list C = Zone (geographical regions of the world: W Europe, N America and S America, let's say)

Changing any of these drop down lists pulls through the relevant results. List A (the months) is higher in the heirarchy than lists B or C, which both use A to get their results. There are three different results pulled through:

-Baseline value (an absolute value using a simple lookup using INDEX/MATCH)
-Last month value (calculated by dividing the sum of a particular column by the sum of another particular column)
-Current month value (calculated by dividing the sum of a particular column by the sum of another particular column)

The above three results are column headers in the reports for each month. The problem is, that the column that these column headers occupy changes every week (but not the rows that they occupy).

For the first data retrieval, which is just the overall total for the months, I am using the following formula:

=INDEX(INDIRECT("'"&$B$3&"'!2:2"),MATCH(B6,INDIRECT("'"&$B$3&"'!1:1"),0))

In this formula, B3 contains the drop-down list of the months, B6 contains 'Current Value' (which is a column header on the various worksheets), row 1:1 contains the various column headings (as there are many more than just the ones I am aiming to retrieve) and row 2:2 contains the sums of those columns (on the 'months' worksheets).

So, this formula is returning the 'Current Value' for any given month for all categories and all zones. This part is the part I am doing OK with. The problem arises when I try to retrieve the 'Current Value' for, let's say, 'June', 'Category B' and, say, N America.

This is where I need to try and introduce SUMIF into my formula. In very simple words, what I want to do is tell Excel to do the following:

1. Search on the worksheet that is named in 'drop-down list A' for the position of the column entitled 'X' (column X divided by Y forms the 'Current Value' column as above)
2. Sum up the values in column 'X' that match 'Category B'. Let's call this figure FI
3. Sum up the values in column 'Y' that match 'Category B'. Let's call this figure FII
4. Divide FI/FII to create the contribution of 'Category B' in June towards 'Current Value'

The complication arises because the column position of 'X' and 'Y' changes month to month, so I need to incorporate additional INDEX/MATCH functions as well as the SUMIF function.

Tough one, I know, but I'm hoping someone here might have a good idea for how to get 'round it.

Thanks very much

James