SUMIF, INDIRECT & INDEX/MATCH in one formula?

Start123

Board Regular
Joined
May 14, 2007
Messages
80
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
May 14, 2007
Messages
80
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Board Regular
Joined
May 14, 2007
Messages
80
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use a sharing site like Box.Net or you can just describe what you want to do in words.
 

SLJones

New Member
Joined
Aug 11, 2015
Messages
7
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,087
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top