Having a Countifs Statement Work with a Filter

I have a formula that EE Pros just helped me properly adjust.  It does a great job..... but here is my question.  I want to put a filter on each column then depending on what I filter, I want the formulas to change.  (I'm using the formulas to construct a Dashboard and I do not want to use a pivot table.  So when I filter on a column, how do I get it to only count those variables which are showing?

Thank you in advance..... and here is the original formula:

=countifs(j1:j115,"Sales Rep", B11:B115,"Yes").

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.

Bright01Author Commented:
OK.... maybe more explanation is due here.  I don't want the formulas to change.  I simply want the formula to work on the filtered data.

Does that help?


pls try

=SUMPRODUCT(SUBTOTAL(103,OFFSET(B1,ROW(B1:B115)-ROW(B1),0,1)),--(B1:B115="Sales Rep"),--(J1:J115="Yes"))


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
Bright01Author Commented:
I'll give it a shot!

Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Bright01Author Commented:
Rgonzo.... brilliant!  What does the 103 reference?  My first line of data actually begins at row 12 so I modified your statement to read =sumproduct(subtotal(103,offset(a12,row(A12:a115)-row(a12),0,1)),--(a12:a115="Speaker"),--(B12:b115="Yes"))   That seems to work.  I don't understand the 103 reference.  Is there anything I need to be careful of....?   Like adding lines in the data?

Thank you,

the Subtotal function with 103 does COUNTA on the visible cells of the range

Returns a subtotal in a list or database.

SUBTOTAL(function_num,ref1,[ref2],...])The SUBTOTAL function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Function_num   Required. The number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.Function_num
(includes hidden values) Function_num
(ignores hidden values) Function
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP
Bright01Author Commented:
Great job!  Thanks so much.

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.