SUBTOTAL and then count with criteria
 

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

Hi Jane,

See Debra Dalgleish's fikter functions pages at:

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


---
Regards,
Norman



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

=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?

[...]