changing a sumif formula so that it recognises filtered data

[Corleone]

Hi - I would like to change the formula below so that when the source date is filtered it only returns the sum of what is filtered

=SUMIFS(UPDATE!AB$2:AB$1081,UPDATE!$P$2:$P$1081,$C2)

I tried this as a potential solution
=SUMPRODUCT(SUBTOTAL(109,OFFSET(UPDATE!AB$2,ROW(UPDATE!AB$2:UPDATE!AB$1081)-ROW(UPDATE!AB$2),,1,1)),(UPDATE!$P$2:$P$1081=$C2),UPDATE!AB$2:AB$1081)

but it just returns 0 even if all the source data is unfiltered


Thanks

 

[Corleone]

I also tried =SUBTOTAL(109, 'UPDATE'!AB$2:AB$1081*('UPDATE'!$P$2:$P$1081=$C2))
but im just getting an error message

 

[Fluff]

If you create a helper column on the update sheet using

=SUBTOTAL(103,P2)

You can then use

=SUMIFS(UPDATE!AB$2:AB$1081,UPDATE!$P$2:$P$1081,$C2,UPDATE!AC$2:AC$1081,1)

change the AC to refer to the helper column.

 

[Corleone]

Once again you have produced the goods - I must owe you at least a crate of beers now
Much appreciated

Thanks

 

[Fluff]

Glad to help & thanks for the feedback.