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

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

=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

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