Help with SUMPRODUCT for filtered rows only - Excel 2003

[MunniMagic]

I have two columns of data, one which contains numbers and text (A), and the other which contains numbers only (B).

I currently have this formula which sums the values in A based upon the condition that col B contains a text value. This works.

=SUMPRODUCT(--(ISTEXT(A$25:A$1500)),B$25:B$1500) ..... (I used named ranges but have removed them for this)

What I need to do is to somoehow integrate the SUBTOTAL function into this formula, so that it only sums visible rows. I can't seem to get it to work.

Can you help?

Thanks in advance.

 

[a massive zebra]

Perhaps =SUMPRODUCT(--(ISTEXT(A$25:A$1500)),SUBTOTAL(9,OFFSET(B$25:B$1500,ROW(B$25:B$1500)-ROW(B$25),0,1)))

 

[MunniMagic]

Perhaps =SUMPRODUCT(--(ISTEXT(A$25:A$1500)),SUBTOTAL(9,OFFSET(B$25:B$1500,ROW(B$25:B$1500)-ROW(B$25),0,1)))

That works.

Thank you very much. You have solved something I have been flummoxed with since yesterday.

 

[a massive zebra]

Glad to be of assistance.