SUMIFS - with FinalRow criteria

[Jacko1307]

I have a formula which looks at another worksheet and then completes a calculation if criteria is met.

=SUMIF(DataDrop!$C$3:$C$2824,"=PKD000",(DataDrop!$P$3:$P$2824))

Is there a way of incorporating the FinalRow into this formula as when I have tried in numerous ways it results in an error

I have the FinalRow function set on the DataDrop worksheet however, the monthly data rows change month on month.

Thank you in anticipation

Best Regards,

Martin

 

[RoryA]

Do you actually need it, rather than simply using:

=SUMIF(DataDrop!$C:$C,"=PKD000",DataDrop!$P:$P)

 

[James006]

Hello,

You can use the Indirect function and the Sumproduct function to determine the last row in your DataDrop sheet ...

=SUMPRODUCT(MAX((ROW([COLOR=#333333]DataDrop![/COLOR]$A$1:$A$10000))*([COLOR=#333333]DataDrop![/COLOR]$A$1:$A$10000<>"")))

Hope this will help

 

[Jacko1307]

Apologies I forgot to mention that there is an offset of 3 rows from FinalRow criteria of the data drop in DataDrop worksheet, which has different data functions.

The FinalRow function is on column 1 and the other actions are on column 3 through 22 however' as there are values in lower cells I dont wish to include these values.

Does this make sence ?

Regards,

Martin

 

[RoryA]

Not to me.

Unless you have PKD000 in column C on the same row as values you don't want added for column P, I don't see how it matters whether there are values below.

If you do have that problem, you'd need something like:

=SUMIF(DataDrop!$C$3:INDEX(DataDrop!$C:$C,FinalRow),"=PKD000",DataDrop!$P$3:INDEX(DataDrop!$P:$P,FinalRow))