Lookup with 2 filtered columns

[lembi2001]

Hi all

I have been asked to help with the setup of a budget checking spreadsheet.

The information in the spreadsheet is pulled in from an external source and this works perfectly.

What they would like to do is use filtering to display the remaining budgets left for each account.

I have managed to get so far but I'm now stuck as I can't work out what I have done wrong.

I need to be able to filter the data on up to 2 columns and display the remaining budget for this account.

The initial budget data is stored on a Worksheet called Data and this sheet contains the budget code, the reference (if applicable), the account name and the budget
The main sheet contains all purchases and costs.

I have stripped out any potentially sensitive or identifiable data from the attached Workbook.

I am using the following formula to display the current account budget but it isn't taking into account the reference:

Please Login or Register  to view this content.

I am trying to filter on the Detail Code and Internal External Columns

Is anyone able to help me?

Thanks for looking

[lembi2001]

OK, had a new thought but that doesn't work either:

Please Login or Register  to view this content.

Result:

#N/A

[63falcondude]

If I am understanding correctly, I think you are over complicating it.

=SUBTOTAL(109,G8:G20000)

Will return the sum of the cells that are showing in column G. Once any columns are filtered, the cells that were filtered out will not be included in the sum.

[lembi2001]

That's not it unfortunately.

It needs both columns S and G for the calculation to work correctly. Some of the values in Column G are multiples and these values are the ones that have a secondary ID in Column S

So example data will look like this:

Column G	Column S	Column T
Code	ID	Budget
3031	JUL	2000
3031	POP	1990
3000		1021
2970		1000

I need to be able to split the calculation down even further if the ID column contains info too

[63falcondude]

There is nothing in column S of your sample from post #1.

To help explain what you want, consider uploading a small representative sample of your data along with the desired results based on that sample (which you can enter manually).

Also I recommend updating your "MS-Off Ver" to the version of Excel that you are currently using. You attached a .xlsx (2007+) but your profile shows 2003.

[lembi2001]

Well I am a proper numpty

Didn't copy the whole sheet over just copied the data i could see on screen (was scrolled across).

I have updated the workbook and attached it.

The sheet contains budget information about each particular account.

As some of the accounts are split up into multiple sub accounts I need to be able to display that information easily.

E.G.

Account 3031 is split up into 2 separate accounts

If the user wants to look at the remaining budget for Account 3031, they first need to select Detail Code 3031 and then the sub account they are interested in.

I can't get the formula to work properly though.

[Glenn Kennedy]

Your explanation wasn't too clear to me. But... is this it?

=SUMPRODUCT(SUBTOTAL(9,OFFSET($F$8:$F$422,ROW($F$8:$F$422)-ROW($F$8),0,1)))-SUMPRODUCT(SUBTOTAL(9,OFFSET($J$8:$J$422,ROW($J$8:$J$422)-ROW($J$8),0,1)))

[lembi2001]

Managed to resolve this this morning.

I have had to use Helper cells to pull in the separate pieces of information required from the 2 columns. This means the formula works now for my VLookup and the user is happy.

thanks for the help though