conditional subtotal function

i can't seem to get anything to work.

the data appears as follows:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

=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" <[email protected]> wrote in
message news:[email protected]...
>
> 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.

Hello
I have a similar problem and would very much appreciate any help.

In the file attached TEST.xls there are main objects (Home) and within them there are two other objects (Cats and Dogs)
Each of the objects (Home,Cat,Dog) has an area in m and a Home ID.

My question is to calculate the total sum of area in m taken by cats from all the homes that have cats only and a total sum of area taken by cats from the homes that have both cats and dogs (excluding dog areas).

This could be done by combining conditional sum (IF Name = "Cat*" and SUM column "Area") and subtotal (at each home ID change) functions in Excel. I've spend days trying to make it work.

The provided example is rather short abbreviation. True files deals with hundreds of homes.

Any suggestions are very much appreciated!

Regards

Sebastian