Countif- Visible Cells in Filter mode

[aabbasi]

Hi:

Could someone please let me know the solution for the following problem:

I have values in cell B2:B50 with values such as Quality, Eng, Purchasing etc. When i go in Auto filter based on Coulmn A2:A50 (with July only) and count "Quality" manually under B2:B50, the answer is 26. But if I write formula Countif(B2:B50, "Quality") I get answer 41.

Is there a way to use Countif function, if I am in the Autofilter mode so as it counts only that rows which are visible under Autofilter and not ALL rows.

Thank you

 

[mastabog]

I gave the complete usage scenario above, with sizes and conditions, but your example is fine too. I'm already using defined names and there is no improvement. Apparently it is known that operations on arrays in Excel are slow.

Last question, how do I condition on sub-strings? Concretely, in your post #42, how do I replace (B2:B50="Quality") to a condition that:

- matches cells that contain the sub-string "Quality" rather than being equal to "Quality"?
- matches cells that do not contain the sub-string "Quality"?

I tried (SEARCH("Quality",B2:B50)>0) and (...=0) and other similar things but didn't work. Neither did (B2:B50="*History*") or <>"*History*".

 

[Aladin Akyurek]

I gave the complete usage scenario above, with sizes and conditions, but your example is fine too. I'm already using defined names and there is no improvement. Apparently it is known that operations on arrays in Excel are slow.

No, I'm not suggesting using defined names. What I was suggesting is something different: avoiding calculations Fvec and FilterRef stand for in multiple cells.

Last question, how do I condition on sub-strings? Concretely, in your post #42, how do I replace (B2:B50="Quality") to a condition that:

- matches cells that contain the sub-string "Quality" rather than being equal to "Quality"?
- matches cells that do not contain the sub-string "Quality"?

I tried (SEARCH("Quality",B2:B50)>0) and (...=0) and other similar things but didn't work. Neither did (B2:B50="*History*") or <>"*History*".

1.

I already gave an example of this...

ISNUMBER(SEARCH("Quality",B2:B50))...

Thus:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B2:B50)-ROW(B2),0)),--ISNUMBER(SEARCH("Quality",B2:B50)))

2.

1-ISNUMBER(SEARCH("Quality",B2:B50))...

Thus:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B2:B50)-ROW(B2),0)),1-ISNUMBER(SEARCH("Quality",B2:B50)))

 

[thestranger66]

Sorry for resurrecting such an old thread, but I am trying to solve a similar problem and this seems to be the best place to ask the question.

In my case, I would like to utilize the formula provided above (=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-MIN(ROW(B2:B7)),,1))*(B2:B7="Quality"))) to determine whether the cells in my range have any value in them at all. However, it seems as though this formula will not accept a wildcard operator such as "?*". If I modify the formula so that the last part of the function changes from (B2:B7="Quality") to (B2:B7="?*") the formula returns a 0. Any ideas on how to implement such an operator?

Thanks in advance for any time devoted to helping me through this issue.

-Steve

 

[Greg Truby]

What are you trying to screen for? If it's just any text at all then you could use ISBLANK(B2:B7) or perhaps NOT(ISBLANK(B2:B7)) or perhaps LEN(B2:B7) <> 0 depending on what your looking for.

If you're scanning for particular text then something along the lines of ...ISNUMBER(FIND("MyText",B2:B7))...

 

[thestranger66]

Hi Greg,

Thank you so much for the quick reply. Using ISBLANK doesn't solve the problem for me because each of these cells is filled with a formula that returns "" if its conditions are not met. If I use the ISBLANK formula on one of those cells where the formula returns a blank, the ISBLANK formula returns FALSE where I need it to return a TRUE. Using "=Countif(B2:B7, "?*")" works for me until I start filtering. The formula in the cell may return text strings or numbers.

Steve

 

[Aladin Akyurek]

[...]

In my case, I would like to utilize the formula provided above (=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7,ROW(B2:B7)-MIN(ROW(B2:B7)),,1))*(B2:B7="Quality"))) to determine whether the cells in my range have any value in them at all. However, it seems as though this formula will not accept a wildcard operator such as "?*".

[...]

You can try:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B2:B50)-ROW(B2),0)),--ISNUMBER(SEARCH("?*",B2:B50)))

which will text values or numbers if any excepting formula blanks (i.e. "").

 

[thestranger66]

Hi Aladin,

Your solution appears to work perfectly.

Thanks!

 

[Aladin Akyurek]

Hi Aladin,

Your solution appears to work perfectly.

Thanks!

You are welcome. Thanks much for the update.

 

[bmcmay]

Hello Aladin,
And sorry for yet another resurrection on this thread. Love your formula thank you! BUT I am running into a situation when I select multiple filters the formula no longer works and give incorrect results.
When I select Market in Column "A" no problem but when I select a 2nd filter for account in Column "D" that is when the formula has problems and give incorrect results.

here is the formula I use
=SUMPRODUCT(SUBTOTAL(103,<wbr style="font-family: Arial, sans-serif; font-size: 16px;">OFFSET(J3:J16000,ROW(J3:<wbr style="font-family: Arial, sans-serif; font-size: 16px;">J16000)-MIN(J3:J16000),,1))*(<wbr style="font-family: Arial, sans-serif; font-size: 16px;">J3:J16000>9.9))

Thank you

 

[Aladin Akyurek]

Hello Aladin,
And sorry for yet another resurrection on this thread. Love your formula thank you! BUT I am running into a situation when I select multiple filters the formula no longer works and give incorrect results.
When I select Market in Column "A" no problem but when I select a 2nd filter for account in Column "D" that is when the formula has problems and give incorrect results.

here is the formula I use
=SUMPRODUCT(SUBTOTAL(103,<wbr style="font-family: Arial, sans-serif; font-size: 16px;">OFFSET(J3:J16000,ROW(J3:<wbr style="font-family: Arial, sans-serif; font-size: 16px;">J16000)-MIN(J3:J16000),,1))*(<wbr style="font-family: Arial, sans-serif; font-size: 16px;">J3:J16000>9.9))

Thank you

It should be either

=SUMPRODUCT(SUBTOTAL(103,OFFSET(J3:J16000,ROW(J3:J16000)-MIN(ROW(J3:J16000)),,1))*(J3:J16000>9.9))


or...

=SUMPRODUCT(SUBTOTAL(103,OFFSET(J3,ROW(J3:J16000)-ROW(J3),0,1))*(J3:J16000>9.9))​

​