Index Match to calculate data - Filter calculate

**Tgbell** · 07-03-2019, 10:35 AM

Hello,
(I am attaching a file to explain my problem even more so). Basically, I have a huge list of data that changes daily due to new downloads (Sheet2 or data). I have the calculated values as the exact formulas on (NOTES sheet). These calculate per the entire data sheet. I need to be able to have a cell with maybe a drop down list, using index match to re-calculate per column k values(sheet2 or data sheet). That way i can choose either inc 1 or inc 2 or both to calculate value, almost like a filter calculate per filter. I will eventually add this to vba. I do not know if any of this is possible, but any help or explanations will be greatly accepted.

Thanks

**Bo_Ry** · 07-03-2019, 01:05 PM

Please select Company criteria in I2:I4

B3
=AGGREGATE(15,6,Sheet2!I2:I99/(Sheet2!E2:E99>A3)/(Sheet2!G2:G99<=A4)/ISNUMBER(MATCH(Sheet2!$K$2:$K$99,Notes!$I$2:$I$5,)),1)

C3
=AGGREGATE(14,6,Sheet2!I2:I99/(Sheet2!E2:E99>A3)/(Sheet2!G2:G99<=A4)/ISNUMBER(MATCH(Sheet2!$K$2:$K$99,Notes!$I$2:$I$5,)),1)

D3
=SUMPRODUCT(Sheet2!I2:I99,(Sheet2!E2:E99>A3)*(Sheet2!G2:G99<=A4)*ISNUMBER(MATCH(Sheet2!$K$2:$K$99,Notes!$I$2:$I$5,)))/F3

E3
=SUMPRODUCT(Sheet2!J2:J99,(Sheet2!E2:E99>A3)*(Sheet2!G2:G99<=A4)*ISNUMBER(MATCH(Sheet2!$K$2:$K$99,Notes!$I$2:$I$5,)))/F3

F3
=SUMPRODUCT((Sheet2!E2:E99>A3)*(Sheet2!G2:G99<=A4)*ISNUMBER(MATCH(Sheet2!$K$2:$K$99,Notes!$I$2:$I$5,)))

**Tgbell** · 07-03-2019, 01:15 PM

Thank you so much! how can I link the data validation like on yours?

**Bo_Ry** · 07-03-2019, 01:34 PM

Create list of Company, Menu > Data > Data Validation > List > Source =range of company list.

**Tgbell** · 07-03-2019, 01:46 PM

Thank you so much, for copying down the formulas in B and other columns what would I need to change in the code. or in the columns on Sheet2 if the date is 2020 as opposed to 2019, how could i show the specific company calculations and fill down? I just keep getting errors, because I need to pull all the INC's calculations for specific dates 2019-2025+.

Index Match to calculate data - Filter calculate

LinkBack

Thread Tools

Rate This Thread

Display

Index Match to calculate data - Filter calculate

Re: Index Match to calculate data - Filter calculate

Re: Index Match to calculate data - Filter calculate

Re: Index Match to calculate data - Filter calculate

Re: Index Match to calculate data - Filter calculate

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Index Match to calculate allocation

[SOLVED] Using index match to calculate years of service

INDEX with multiple MATCH's used with IF's taking a really long time to calculate

[SOLVED] Should I use VLOOKUP or INDEX and MATCH (array) to calculate the data I need? (Part 1?)

sumproduct to calculate payout % or index match?

[SOLVED] Index/Match - To calculate variance

Calculate month to date returns, index and match

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions