conditional subtotal function

[dreamz]

i can't seem to get anything to work.

the data appears as follows:


Code
-------------------

A B
ON 1000
ON 1000
OFF 500
OFF 500
ON 1000
OFF 1000
OFF 400
OFF 300
ON 400

-------------------


now, i have an autofilter that will filter some of these out and leav
the rest, e.g.:


Code
-------------------

A B
OFF 500
OFF 500
ON 1000
OFF 1000
ON 400

-------------------


now i need to calculate a sum with the condition that the value i
column a is "on". here, the answer should be 1400.

the usual array formula with a sum doesn't work because it sums hidde
cells as well. the only function i know of that sums correctly is th
subtotal function, but i don't know how to add the "on"-only conditio
to it.

any ideas? thanks

 

[davesexcel]

http://www.contextures.com/xlFunctions01.html

Check this page for info to sum a filtered list

 

[Bob Phillips]

=SUMPRODUCT((SUBTOTAL(9,OFFSET($B$1,ROW($B$2:$B$20)-ROW($B$1),,1)))*(A2:A20=
"ON"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[dreamz]

thanks, dave! i'll be reading through that site to better understand th
functions.

bob, that worked perfectly. now to figure out exactly what it means
thanks again