Dynamic Sumproduct Lookup

Hi All,

I am trying, what I thought was going to be easy, to do a sumproduct, but trying to lookup the variables.

Here is what I mean.

In Row 1, starting in Column B is the month.year. In Column A, starting in Row 2 is the customer. There can be hundreds of customers and the month.year can very in the raw data.

I want the formula to find the month.year then add up all the values where it sees the customer name. I normally would use a sumproduct because it would be a column for column reference, but I don't know which column the date may fall in on the data page.

Attached is a sample. Summary is where the formula takes place.

Thoughts?

Thanks,
Matt

For large matrices do everything in your power to avoid using SUMPRODUCT given it is an "expensive" formula... use alternative methods (Pivots, helper cells etc...)

In this case I think you will find a basic SUMIF will suffice - you need just use an INDEX call to create the appropriate sum_range in the SUMIF.

Please Login or Register  to view this content.

The calculation time of the above will be virtually instantaneous... even so it would make sense to store the MATCH of month.year in a new row on Summary sheet (ie above below the month.year header).
This way you calculate the MATCH only once per column (the result is a constant for all rows) referring to the result cell in the subsequent matrix calcs as opposed to repeatedly calculating the match result.
(avoiding repetitive calculations is one of the golden rules when it comes to optimising performance of your model)

When you use Named Ranges you must be careful regards their relative positions.

You have three named ranges in use:

Customer:
=OFFSET(Data!$A$1,1,0,COUNTA(Data!$A:$A)-1,1)
range is A2:A?

Month:
=OFFSET(Data!$A$1,0,1,1,COUNTA(Data!$1:$1)-1)
range is B1:?1

DataSet:
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))
range is A1:??

Given the above alone you might spot the issue but If we work through using the example of the below we can see the errors more clearly.
(I will look only at SUMIF given this is the relevant part)

Summary!B2:
=SUMIF(Customer,$A2,INDEX(DataSet,0,MATCH(B$1,Month,0)))

The MATCH of Month will be conducted against B1:?1 range... in the case of 1.2007 this will return 13

The range argument in the SUMIF will be applied to A2:A? range.... in the case of Customer A and the source data this generates an array of:

{"A";"B";"C";"D";"A";"B";"F"}

The issues start to arise however when you generate the sum_range given

a) the DataSet Range commences in Row 1 whereas the Customer Range commences in Row 2

b) the DataSet Range commences in Column A whereas the Month Range commences in Column B

The result is that the sum_range is in effect out of kilter from the criteria range by 1 row and 1 column.

So for Customer A / 1.2007 you are in fact returning a sum_range of:

{12.2006;1200;1800;2700;4050;6075;9112.5;13668.75}

the first value being row 1 of 1.2006 and so on and so forth.

So, to correct, the simplest solution is to adjust DataSet named range either by

a) making this the range used for all calculations (leaving as A1:??), eg:

=IF(ISNUMBER(MATCH(B$1,INDEX(DataSet,1,0),0)),SUMIF(INDEX(DataSet,0,1),$A2,INDEX(DataSet,0,MATCH(B$1,INDEX(DataSet,1,0),0))),0)

(ie get rid of Customer / Month in this calculation altogether)

or

b) adjust DataSet such that it commences from $B$2 rather than $A$1 (ie first data point)