SUBTOTAL with SUM IFs?

[jeffe]

I am having trouble getting the SUBTOTAL function to work with a SUM IF statement.

I would like to get a count of each cell that is greater than 14 but less than 21. Here’s what I have been using

{=SUM(IF(A$1:A$1000>14,IF(A$1:A$1000<21,1,0),0))}​

However, I would like to only count cells after certain filters have been applied, but I’m getting lost in the talk about SUMPRODUCT, OFFSET, among other functions I have seen on forums to make this work.

Would you be able to explain how I get this to work?

 

[T. Valko]

I am having trouble getting the SUBTOTAL function to work with a SUM IF statement.

I would like to get a count of each cell that is greater than 14 but less than 21. Here’s what I have been using

{=SUM(IF(A$1:A$1000>14,IF(A$1:A$1000<21,1,0),0))}
​

However, I would like to only count cells after certain filters have been applied, but I’m getting lost in the talk about SUMPRODUCT, OFFSET, among other functions I have seen on forums to make this work.

Would you be able to explain how I get this to work?

Try this...

=SUMPRODUCT(SUBTOTAL(2,OFFSET(A1:A1000,ROW(A1:A1000)-ROW(A1),0,1)),--(A1:A1000>14),--(A1:A1000<21))

 

[jeffe]

Thank you. I'm not fully sure how that worked, but it did. I'll have to look at that some more.

Thank you!!

 

[T. Valko]

Thank you. I'm not fully sure how that worked, but it did. I'll have to look at that some more.

Thank you!!

You're welcome. Thanks for the feedback!