conditional subtotal function
 

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 leave
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 in
column a is "on". here, the answer should be 1400.

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

any ideas? thanks.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=572446

conditional subtotal function
 

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

Check this page for info to sum a filtered list


--
davesexcel


------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=572446

conditional subtotal function
 

=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" wrote in
message ...

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 leave
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 in
column a is "on". here, the answer should be 1400.

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

any ideas? thanks.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile:
http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=572446

conditional subtotal function
 

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

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


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=572446