# 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.

Andy
###### 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.

Commented:
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
Commented:
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