SumProduct & Index functions

[[G]rumpy [O]ld [D]uffer]

I have an EXCEL file (call this "Database") that in a simplified form
looks like this (Col. & Row Headings shown):-

A B C D E F G
1 Day Loc. 1 Loc. 2 Loc. 3 Loc. 4 Loc. 5 Loc. 6
2 1 920 1694 954 409 121 903
3 2 180 283 174 32 14 338
4 3 993 1654 864 324 73 878
5 4 78 111 102 10 2 157
6 5 1205 1731 1017 461 183 932
7 1 175 252 189 35 10 327
8 2 1046 1340 746 332 51 800
9 3 1098 1722 938 369 86 843
10 4 172 255 165 28 7 277
11 5 1095 1903 941 412 103 1027

I want to link to this database from an external EXCEL file (call this
"FrontView"). "Frontview" is to show numbers depending on what
selections are made from 2 Drop-Down boxes which list the Day & Loc.

I'm aware of the HLOOKUP & VLOOKUP problems when linking to external
files that are not open, and have used SUMPRODUCT to overcome this.
However, I want to display the 'Loc.' figures depending on which is
selected in one of the Drop-Down boxes in "Frontview", so I guess I'm
looking for some sort of INDEX statement within a SUMPRODUCT
statement, so that if Day 1 & Loc. 5 is selected the return will be
131 (121+10); if Day 1 & Loc. 3 is selected the return will be 1,143
(954+189) etc. etc.

Thanks to this Group for the help you've provided in the past, and
hope you can help in resolving this 'little' problem.

 

[Domenic]

Hi,

Try the following formula in your Frontview file:

=SUMIF([Database.xls]Sheet1!A$2:A$11,B1,INDEX([Database.xls]Sheet1!B$2:G$
11,0,MATCH(B2,[Database.xls]Sheet1!B$1:G$1,0)))

where B1 contains the day and B2 contains the loc.

Hope this helps!