asked on

Count only visible cells with matching criteria

Hi Experts!

Need some help with a formula that is driving me crazy today. I am hoping that it is a "senior moment" and this is an easy fix.

I have a table on a worksheet "dashboard". The Managers names frun from B4 to B12. What I need to do is count the number of visible cells on the tab "stockouts" in column f4:f5000. The managers name are in A4:A5000 on the stockout tab. Column F is just "yes" or "no". I need to have the formula is "dashboard--cell H2:H12"

This is what I have so far but it is giving me a number that is way off.

=SUMPRODUCT((Stockouts!$A$4:$A$4424=Dashboard!$B4)--SUBTOTAL(3,OFFSET(Stockouts!$A$4,ROW(Stockouts!$A$4:$A$4424),0,1)))--(Stockouts!$F$4:$F$4424="YES")

Any help is appreciated.

Thanks in advance!

Andy

Ejgil Hedegaard

I don't know what you mean by visible cells, do you filter the list?
If so, you could use the function 103 instead of 3 for the Subtotal.
103 counts only visible cells.

As I read it, you want to count the number of "YES" in column F for each manager in column A, for all managers on sheet Dashboard.
You could use =COUNTIFS(Stockouts!$A$4:$A$4424,Dashboard!$B4,Stockouts!$F$4:$F$4424,"YES")
or just the columns
=COUNTIFS(Stockouts!$A:$A,Dashboard!$B4,Stockouts!$F:$F,"YES")

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

spudmcc

ASKER

Spot on! Thanks again for the help! I see where my "brain fade" occurred.

Andy