asked on

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").

B.

Bright01

ASKER

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?

B.

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bright01

ASKER

I'll give it a shot!

B

Bright01

ASKER

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,

B.

Rgonzo1971

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

definition
Returns a subtotal in a list or database.

Syntax
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
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

Bright01

ASKER

Great job! Thanks so much.

B.