SumProduct & Index functions

  • Thread starter Thread starter [G]rumpy [O]ld [D]uffer
  • Start date Start date
G

[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.
 
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!
 
Back
Top