subtotal sumproduct

[neilp]

Hi

I have some data in Column J that I need to divide by the figures in Column D

=SUMPRODUCT(J4:J29/$D$4:$D$29) seems to do the job.

However I need it to work when the data is filtered.

I'm sure it must have something to do with =subtotal, but cannot figure it out.

Any ideas?

thanks

Neil

 

[Fluff]

How about

=SUM(J4:J29/D4:D29*MAP(D4:D29,LAMBDA(m,SUBTOTAL(103,m))))

 

[neilp]

that is brilliant! no other word for it. Brilliant
thanks
N

 

[Fluff]

You're welcome & thanks for the feedback.