Solved

# Count only visible cells with matching criteria

Posted on 2013-09-17
Medium Priority
468 Views
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
0
Question by:spudmcc

LVL 25

Expert Comment

ID: 39500759
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

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 39500841
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

Author Closing Comment

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

Andy
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.