Count If for Filtered cells

[howerd]

Hi,

I was wondering if someone can help me on a formula I am trying to use.

I am trying to incorporate a countif formula, which only counts cells which have showing when the filter is applied. I have the following formula which appears to work:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($AB$3:$AB$500,ROW($AB$3:$AB$500)-ROW($AB$3),0,1)),--($AB$3:$AB$500=B4))

Where I am struggling is when I try to 'count' cells which have data between two figures, for example greater than or equal to 6 and less than or equal to 10. I have tried the following formula

=SUMPRODUCT(SUBTOTAL(3,OFFSET($G$3:$G$500,ROW($G$3:$G$500)-ROW($G$3),0,1)),--(($G$3:$G$500>=6)*AND(G3:$G$500<=10)))

This appears to work when the figures are below 10, however if one of the cells goes above 10, it shows a count of 0?

I am sure it is a simple error, but any help would be greatly appreciated.

Thanks.

 

[Andrew Poulsom]

Try removing the AND.

 

[Yahya]

without AND
=SUMPRODUCT(SUBTOTAL(3,OFFSET($G$3:$G$500,ROW($G$3:$G$500)-ROW($G$3),0,1)),--(($G$3:$G$500>=6)*(G3:$G$500<=10)))

 

[howerd]

Hi Andrew / Yahya,

That's a great help, thank you very much! (new it would be something simple!)

Cheers

Chris

 

[Yahya]

Try removing the AND.

sorry andrew me and you at same time

Hi Andrew / Yahya,

That's a great help, thank you very much! (new it would be something simple!)

Cheers

Chris

you are welcome