subtotal & sumproduct

[scoobyblue]

Probably quite simple this but I've been unable to join the two. I have values in column E that I want to total when filtered (subtotal(109...) but only when Column N has a "YES" in the cell. I guess I may need MATCH or INDEX, any help greatly appreciated.

Thanx,
scoobyblue

 

[DonkeyOte]

I believe you would need something along the lines of:

=SUMPRODUCT(SUBTOTAL(109,OFFSET($E$2:$E$250,ROW($E$2:$E$250)-MIN(ROW($E$2:$E$250)),,1))*($N$2:$N$250="Yes"))

Alter ranges to suit but note you can't use entire column references pre XL2007 (nor should you want to as performance will be affected).

Note: OFFSET is Volatile

 

[scoobyblue]

that's great - many thanx.