How can I count cells in a filtered column within a criteria...

I've got a formula to count cells in a filtered column within a criteria (<=2):
=SUMPRODUCT((SUBTOTAL(3,OFFSET(I18:I42,ROW(I18:I42)-MIN(ROW(I18:I42)),0,1)))*(I18:I42={0,1,2}))

But now I need to count cells in a filtered column that meet the criteria of having 'Y' in the cell, as in for yes or no. Does anyone have a formula for that or can someone help me modify the formula I have? Thanks soooooooo much!!!
Lizette

Assuming that the new criterion is in addtion to the old one, and that Column J contains your "Y" and "N" values...

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(I18:I42,ROW(I18:I42 )-MIN(ROW(I18:I42)),0,1))),--(I18:I42<=2),--(J18:J42="Y"))

Hope this helps!

Originally Posted by lizettegr

I've got a formula to count cells in a filtered column within a criteria (<=2):
=SUMPRODUCT((SUBTOTAL(3,OFFSET(I18:I42,ROW(I18:I42)-MIN(ROW(I18:I42)),0,1)))*(I18:I42={0,1,2}))

But now I need to count cells in a filtered column that meet the criteria of having 'Y' in the cell, as in for yes or no. Does anyone have a formula for that or can someone help me modify the formula I have? Thanks soooooooo much!!!
Lizette

Hey domenic, thanks again for your help. however the Y criteria is not in addition to the less than or equal to 2. I just need to count the cells that have a Y in them. Can you still help? how do you know soo much!

Assuming that Column J contains your "Y" and "N" values (you didn't specify )...

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(I18:I42,ROW(I18:I42 )-MIN(ROW(I18:I42)),0,1))),--(J18:J42="Y"))

Adjust the reference, if needed.

Hope this helps!

Originally Posted by lizettegr

Hey domenic, thanks again for your help. however the Y criteria is not in addition to the less than or equal to 2. I just need to count the cells that have a Y in them. Can you still help? how do you know soo much!

That Works Perfectly! Thanks!