SumProduct with dynamic last row for data in separate worksheet

[TIFinance]

I'm using the following SumProduct formula and need the formula to update based on the last rows of data in a 2nd worksheet. The cells in bold are where the reference for the last row with data in the 2nd tab (Transactions Export) should be updating. For instance, if I copy paste a data dump that has 10 rows of data, I need the formula to adjust to D2:D11 and E2:E11. Is there some sort of VBA code I can use to lookup the last row in each of those columns and update the formula accordingly? Any help much appreciated.

Please Login or Register  to view this content.

[teylyn]

Hello,

I suggest you use dynamic range names. Use the Name Manager in the Formulas ribbon and create these two names (I assume row 1 holds labels for the columns)

TransactionsD ='Transactions Export'!$D$2:INDEX('Transactions Export'!$D:$D,COUNTA('Transactions Export'!$D:$D))
TransactionsE =OFFSET(TransactionsD,0,1)

Then you can use

Please Login or Register  to view this content.

You could build the Index formula straight into the Sumproduct, too, but it's easier to read and maintain with named ranges.

cheers, teylyn

[Tony Valko]

You could use dynamic ranges.

Name: Range1 (or whatever you want to call it)
Refers to:

='Transactions Export'!$D$2:INDEX('Transactions Export'!$D$2:$D$50000,MATCH(1E100,'Transactions Export'!$D$2:$D$50000))

Name: Range2 (or whatever you want to call it)
Refers to:

='Transactions Export'!$E$2:INDEX('Transactions Export'!$E$2:$E$50000,MATCH(1E100,'Transactions Export'!$E$2:$E$50000))

Adjust for reasonable end of ranges where I use down to row 50000.

Then your formula becomes:

=SUMPRODUCT(--(MONTH(Range1)=MONTH($A$1)),Range2)

[teylyn]

With Sumproduct I would only use as many rows as have data. Otherwise the formula can become pretty slow.

[Tony Valko]

With Sumproduct I would only use as many rows as have data.

We don't know how many rows of data there are.

That's why we're using dynamic ranges!

[TIFinance]

Is there a way I can offset to end the ranges on the 2nd to last row? It turns out using the last row doesn't work because only some of the columns have data in them (it's just a totals row which I don't need). D has one less row of data than column E so SumProduct returns #VALUE! unless they match.

[Jonmo1]

Make both named ranges refer to the same column for the last row. (probably D since it has the fewer rows)

Using Tony's example

Name: Range1 (or whatever you want to call it)
Refers to:

='Transactions Export'!$D$2:INDEX('Transactions Export'!$D$2:$D$50000,MATCH(1E100,'Transactions Export'!$D$2:$D$50000))

Name: Range2 (or whatever you want to call it)
Refers to:

='Transactions Export'!$E$2:INDEX('Transactions Export'!$E$2:$E$50000,MATCH(1E100,'Transactions Export'!$D$2:$D$50000))

[samba_ravi]

=SUMPRODUCT((MONTH('Transactions Export'!D2:INDEX('Transactions Export'!D:D,MATCH(10^10,'Transactions Export'!D:D)))=(MONTH($A$1)))*('Transactions Export'!E2:INDEX('Transactions Export'!E:E,MATCH(10^10,'Transactions Export'!D:D))))

Please Login or Register  to view this content.

TRY THE ABOVE FORMULA

[TIFinance]

Thanks all - that adjustment works perfectly!

[Tony Valko]

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.