SumProduct and VLookup for selected columns

I have a function that reads:

=SUMPRODUCT(VLOOKUP(D17,'D:\Temporary Storage - not backed up and unsecured\[PT current plan.xls]Ore Fines'!$A$12:$BC$146,{4,8,12,16,20,24,28,32,36,40,44,48,52},FALSE)+VLOOKUP(D17,'D:\Temporary Storage - not backed up and unsecured\[PT current plan.xls]Lump Ores'!$A$12:$AY$146,{4,8,12,16,20,24,28,32,36,40,44,48},FALSE))

The aim is to look for the matching date (Column D) in each relevant tab, then add up the cells corresponding to a column heading of "usage" only (the column heading repeat every 4 cells).

This returns the #N/A error; I am uncertain as to how to rectify this.

I have checked that the names, file paths, rows and columns are correct, and that any hidden rows, columns or tabs are clearly visible.

Any ideas?

Edit 1

I have use the "Evaluate Formula" Tool which gives the following results. The first stage in the evaluation yields:

SUMPRODUCT(VLOOKUP(41370,'D:\Temporary Storage - not backed up and unsecured\[PT current plan.xls]Ore Fines'!$A$12:$BC$146,{4,8,12,16,20,24,28,32,36,40,44,48,52},FALSE)+VLOOKUP(D17,'D:\Temporary Storage - not backed up and unsecured\[PT current plan.xls]Lump Ores'!$A$12:$AY$146,{4,8,12,16,20,24,28,32,36,40,44,48},FALSE))

Stepping into this shows the corresponding date: 6 Apr 2013 (the date in cell D17)

The second stage: SUMPRODUCT(#REF!+VLOOKUP(D17,'D:\Temporary Storage - not backed up and unsecured\[PT current plan.xls]Lump Ores'!$A$12:$AY$146,{4,8,12,16,20,24,28,32,36,40,44,48},FALSE))

the third stage: SUMPRODUCT(#REF!+VLOOKUP(41370,'D:\Temporary Storage - not backed up and unsecured\[PT current plan.xls]Lump Ores'!$A$12:$AY$146,{4,8,12,16,20,24,28,32,36,40,44,48},FALSE))

The fourth stage: SUMPRODUCT(#REF!+#REF!)

The fifth stage: SUMPRODUCT(#REF!)

And finally: #N/A

Answers


Wrap SUM() around the VLOOKUPs:

=SUMPRODUCT(SUM(VLOOKUP(D17,'D:\Temporary Storage - not backed up and unsecured\[PT current plan.xls]Ore Fines'!$A$12:$BC$146,4,8,12,16,20,24,28,32,36,40,44,48,52},FALSE))+SUM(VLOOKUP(D17,'D:\Temporary Storage - not backed up and unsecured\[PT current plan.xls]Lump Ores'!$A$12:$AY$146,{4,8,12,16,20,24,28,32,36,40,44,48},FALSE)))

When you have arrays of different sizes, when both are larger than one element long, you will get an error, for example:

=SUMPRODUCT({1,2}+{1,2,3})

Will give an error because the arrays are not of the same size. A quick fix is the SUM():

=SUMPRODUCT(SUM({1,2})+SUM({1,2,3}))

Which adds up each element in the arrays first.


Need Your Help

Displaying memory in rows and columns, with starting address of each row displayed at the beginning of each line

c++ class rows member-functions

I'm currently writing a program which uses a class to simulate the operation of an 8-bit microcontroller. I had to create 5 member functions - setPC, show, loadFile, step and constructor. I'm having

GET pull request merge commit sha from pull request number using github api

git github-api git-revert hubot

I'm trying to use the github api (via githubot https://github.com/iangreenleaf/githubot) to GET a pull request merge commit sha from the pull request number.