Count only visible cells with matching criteria

Posted on 2013-09-17
Medium Priority
Last Modified: 2013-09-17
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.


Any help is appreciated.  

Thanks in advance!

Question by:spudmcc
LVL 25

Expert Comment

by:Ejgil Hedegaard
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
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


regards, barry

Author Closing Comment

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


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

Join & Write a Comment

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question