SUBTOTAL and then count with criteria

[Guest]

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

 

[Guest]

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

 

[Aladin Akyurek]

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

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?

[...]