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
spudmccAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
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")
0
barry houdiniCommented:
Hello Andy,

You need to separate the criteria with commas.....and the OFFSET function isn't quite right. Try this version

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

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spudmccAuthor Commented:
Spot on!  Thanks again for the help!  I see where my "brain fade" occurred.  

Andy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.