SUBTOTAL and then count with criteria

[Jane]

Afte auto-filtering specific categories, I then need to count values <=1200,
<=3600, etc. I'm familiar with SUBTOTAL but basically need a SUBTOTALIF type
of formula.

any help is appreciated

jane

[Norman Jones]

Hi Jane,

See Debra Dalgleish's fikter functions pages at:

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


---
Regards,
Norman



"Jane" <[email protected]> wrote in message
news:[email protected]...
> Afte auto-filtering specific categories, I then need to count values
> <=1200,
> <=3600, etc. I'm familiar with SUBTOTAL but basically need a SUBTOTALIF
> type
> of formula.
>
> any help is appreciated
>
> jane

[Jane]

Norman,
here's the formula I've tried a few times with no luck. I am sure I'm
missing something adn not suing correctly.

=sumproduct(subtotal(3,offset(C6:C11,row(C6:C11)-MIN(Row(C6:C11),,1)),--(C6:C11=C6))

In C I have a number of class # designations and have filtered to bring up
1006 - the D column shows the values to the right
1006 1200
1006 600
1006 3600
1006 100
1006 2400
1006 1200

given this, how would I apply the calculation? where do I specfiy that I
want >1200, or other criteria such as that?

Thank you for your help! jane





"Norman Jones" wrote:

> Hi Jane,
>
> See Debra Dalgleish's fikter functions pages at:
>
> http://www.contextures.com/xlFunctions04.html
>
>
> ---
> Regards,
> Norman
>
>
>
> "Jane" <[email protected]> wrote in message
> news:[email protected]...
> > Afte auto-filtering specific categories, I then need to count values
> > <=1200,
> > <=3600, etc. I'm familiar with SUBTOTAL but basically need a SUBTOTALIF
> > type
> > of formula.
> >
> > any help is appreciated
> >
> > jane
>
>
>

[Aladin Akyurek]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C6:C11,ROW(C6:C11)-MIN(ROW(C6:C11),,1)),--(D6:D11>=1200),--(D6:D11<=3600))

Jane wrote:
> Norman,
> here's the formula I've tried a few times with no luck. I am sure I'm
> missing something adn not suing correctly.
>
> =sumproduct(subtotal(3,offset(C6:C11,row(C6:C11)-MIN(Row(C6:C11),,1)),--(C6:C11=C6))
>
> In C I have a number of class # designations and have filtered to bring up
> 1006 - the D column shows the values to the right
> 1006 1200
> 1006 600
> 1006 3600
> 1006 100
> 1006 2400
> 1006 1200
>
> given this, how would I apply the calculation? where do I specfiy that I
> want >1200, or other criteria such as that?
>
[...]