Subtotal of Hidden Values over a certain amount of days

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

instereo911 - 23 Apr 2007 23:15 GMT

Good afternoon,

I want to subtotal only numbers over 30 that are not hidden and don't know
the formula. Can anyone assist me:

A
1 35
2 65
3 08
4 12
12 38
15 68
16 = SUBTOTAL(?, +30 )

Hopefully that makes sense.

Thanks again for everyones posting on this. This website has saved me from
tons of work many times.

Reply to this Message

T. Valko - 24 Apr 2007 03:32 GMT

The rows are hidden and not filtered, right?

What version of Excel are you using? Excel 2003 extended the SUBTOTAL
function to work on hidden rows as well as filtered rows.

Try this: (I don't have Excel 2003 but this works on Excel 2002 (filtered)
and should work on Excel 2003 (hidden) by using the 100 series argument)

=SUMPRODUCT(SUBTOTAL(103,OFFSET(A1:A20,ROW(A1:A20)-MIN(ROW(A1:A20)),,1)),--(A1:A20>30),A1:A20)

Biff

> Good afternoon,
>
[quoted text clipped - 14 lines]
> Thanks again for everyones posting on this. This website has saved me from
> tons of work many times.

Reply to this Message