Autofilter and count on filtered data

[gr8posts]

I have the following 3 columns table starting at cell A2.
It is a simlified version since the real table is about 40x2000=8000 cells.

- A_B______C
1
2 a_b______c
3 1_#N/A___Ok
4 1_Ok_____Ok
5 3_Ok_____#N/A
6 4_#VALUE_#N/A
7 4_#VALUE_Ok
8 4_#N/A___Ok
9 4_Ok_____Ok

I want to get in row 1 the number of #Error codes per column (b and c) based
on column a filtered data. [If possible A1 should show filtered value.]

To be clear:
In the above table I implement autofilter to the range A3:C9 and after
having filter for value 4 in Column a,
I want to get as result [A1=4] B1=3, C1=1 (column b will have 2 times #VALUE
+ 1 #N/A = 3 and column c will have 1 time #N/A)
If I filter column a for 1 the results will be [A1=1] B1=1, C1=0 and if I
filter for 3 the results will be [A1=3] B1=0 and C1=1.

I don't want to implement individual filters on columns b and c for #Errors

Thank you in advance.
Ps. I am looking for a formula not VBA code

 

[T. Valko]

I assume the 4 in A1 is the sum of B1 and C1?

Enter this formula in B1 and copy across to C1:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B10,ROW(B3:B10)-MIN(ROW(B3)),0,1)),--(ISERROR(B3:B10)))

A1: =B1+C1

 

[gr8posts]

Great,
Thank you for your answer it works as I was expecting. (in cells B1 and C1)

For cell A1 it is not the B1 + C1 but the filtered value in column a
What I did was to set A1=SUBTOTAL(1;A1:A10) (average of filtered values) and
this gives me the expected result in A1 BUT
ONLY if column a contains ONLY numbers AND
ONLY if a filter is active.

Ihope you understand what I mean:If there is no filter active it gives the
Average of column A which is not suitable, and if column A has text it gives
the error #DIV/0! which is normal but not what I would like.

Any suggestion for the correct formula in A1 in case column a contais text
is welcome.

Thank you again for your help.
PS. I use ";" instead of "," since I am with European regional settings

I assume the 4 in A1 is the sum of B1 and C1?

Enter this formula in B1 and copy across to C1:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B10,ROW(B3:B10)-MIN(ROW(B3)),0,1)),--(ISERROR(B3:B10)))

A1: =B1+C1

--
Biff
Microsoft Excel MVP

I have the following 3 columns table starting at cell A2.
It is a simlified version since the real table is about 40x2000=8000
cells.

- A_B______C
1
2 a_b______c
3 1_#N/A___Ok
4 1_Ok_____Ok
5 3_Ok_____#N/A
6 4_#VALUE_#N/A
7 4_#VALUE_Ok
8 4_#N/A___Ok
9 4_Ok_____Ok

I want to get in row 1 the number of #Error codes per column (b and c)
based
on column a filtered data. [If possible A1 should show filtered value.]

To be clear:
In the above table I implement autofilter to the range A3:C9 and after
having filter for value 4 in Column a,
I want to get as result [A1=4] B1=3, C1=1 (column b will have 2 times
#VALUE
+ 1 #N/A = 3 and column c will have 1 time #N/A)
If I filter column a for 1 the results will be [A1=1] B1=1, C1=0 and if I
filter for 3 the results will be [A1=3] B1=0 and C1=1.

I don't want to implement individual filters on columns b and c for
#Errors

Thank you in advance.
Ps. I am looking for a formula not VBA code

 

[T. Valko]

Ok, in other words, you want cell A1 to display what column A is filtered
on?

If the column was filtered on "yes" then you want "yes" returned to cell A1?

--
Biff
Microsoft Excel MVP

Great,
Thank you for your answer it works as I was expecting. (in cells B1 and
C1)

For cell A1 it is not the B1 + C1 but the filtered value in column a
What I did was to set A1=SUBTOTAL(1;A1:A10) (average of filtered values)
and
this gives me the expected result in A1 BUT
ONLY if column a contains ONLY numbers AND
ONLY if a filter is active.

Ihope you understand what I mean:If there is no filter active it gives the
Average of column A which is not suitable, and if column A has text it
gives
the error #DIV/0! which is normal but not what I would like.

Any suggestion for the correct formula in A1 in case column a contais text
is welcome.

Thank you again for your help.
PS. I use ";" instead of "," since I am with European regional settings

I assume the 4 in A1 is the sum of B1 and C1?

Enter this formula in B1 and copy across to C1:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B10,ROW(B3:B10)-MIN(ROW(B3)),0,1)),--(ISERROR(B3:B10)))

A1: =B1+C1

--
Biff
Microsoft Excel MVP

I have the following 3 columns table starting at cell A2.
It is a simlified version since the real table is about 40x2000=8000
cells.

- A_B______C
1
2 a_b______c
3 1_#N/A___Ok
4 1_Ok_____Ok
5 3_Ok_____#N/A
6 4_#VALUE_#N/A
7 4_#VALUE_Ok
8 4_#N/A___Ok
9 4_Ok_____Ok

I want to get in row 1 the number of #Error codes per column (b and c)
based
on column a filtered data. [If possible A1 should show filtered value.]

To be clear:
In the above table I implement autofilter to the range A3:C9 and after
having filter for value 4 in Column a,
I want to get as result [A1=4] B1=3, C1=1 (column b will have 2 times
#VALUE
+ 1 #N/A = 3 and column c will have 1 time #N/A)
If I filter column a for 1 the results will be [A1=1] B1=1, C1=0 and if
I
filter for 3 the results will be [A1=3] B1=0 and C1=1.

I don't want to implement individual filters on columns b and c for
#Errors

Thank you in advance.
Ps. I am looking for a formula not VBA code

 

[gr8posts]

"If the column was filtered on "yes" then you want "yes" returned to cell A1?"

Exactly.
This is what I want if possible.
Thanks again.

Ok, in other words, you want cell A1 to display what column A is filtered
on?

If the column was filtered on "yes" then you want "yes" returned to cell A1?

--
Biff
Microsoft Excel MVP

Great,
Thank you for your answer it works as I was expecting. (in cells B1 and
C1)

For cell A1 it is not the B1 + C1 but the filtered value in column a
What I did was to set A1=SUBTOTAL(1;A1:A10) (average of filtered values)
and
this gives me the expected result in A1 BUT
ONLY if column a contains ONLY numbers AND
ONLY if a filter is active.

Ihope you understand what I mean:If there is no filter active it gives the
Average of column A which is not suitable, and if column A has text it
gives
the error #DIV/0! which is normal but not what I would like.

Any suggestion for the correct formula in A1 in case column a contais text
is welcome.

Thank you again for your help.
PS. I use ";" instead of "," since I am with European regional settings

I assume the 4 in A1 is the sum of B1 and C1?

Enter this formula in B1 and copy across to C1:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B10,ROW(B3:B10)-MIN(ROW(B3)),0,1)),--(ISERROR(B3:B10)))

A1: =B1+C1

--
Biff
Microsoft Excel MVP


I have the following 3 columns table starting at cell A2.
It is a simlified version since the real table is about 40x2000=8000
cells.

- A_B______C
1
2 a_b______c
3 1_#N/A___Ok
4 1_Ok_____Ok
5 3_Ok_____#N/A
6 4_#VALUE_#N/A
7 4_#VALUE_Ok
8 4_#N/A___Ok
9 4_Ok_____Ok

I want to get in row 1 the number of #Error codes per column (b and c)
based
on column a filtered data. [If possible A1 should show filtered value.]

To be clear:
In the above table I implement autofilter to the range A3:C9 and after
having filter for value 4 in Column a,
I want to get as result [A1=4] B1=3, C1=1 (column b will have 2 times
#VALUE
+ 1 #N/A = 3 and column c will have 1 time #N/A)
If I filter column a for 1 the results will be [A1=1] B1=1, C1=0 and if
I
filter for 3 the results will be [A1=3] B1=0 and C1=1.

I don't want to implement individual filters on columns b and c for
#Errors

Thank you in advance.
Ps. I am looking for a formula not VBA code

 

[T. Valko]

Try this array formula** :

Assume the filtered range is A3:A10

=INDEX(A3:A10,MATCH(1,(SUBTOTAL(3,OFFSET(A3:A10,ROW(A3:A10)-MIN(ROW(A3:A10)),0,1)))*(A3:A10<>""),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

"If the column was filtered on "yes" then you want "yes" returned to cell
A1?"

Exactly.
This is what I want if possible.
Thanks again.

Ok, in other words, you want cell A1 to display what column A is filtered
on?

If the column was filtered on "yes" then you want "yes" returned to cell
A1?

--
Biff
Microsoft Excel MVP

Great,
Thank you for your answer it works as I was expecting. (in cells B1 and
C1)

For cell A1 it is not the B1 + C1 but the filtered value in column a
What I did was to set A1=SUBTOTAL(1;A1:A10) (average of filtered
values)
and
this gives me the expected result in A1 BUT
ONLY if column a contains ONLY numbers AND
ONLY if a filter is active.

Ihope you understand what I mean:If there is no filter active it gives
the
Average of column A which is not suitable, and if column A has text it
gives
the error #DIV/0! which is normal but not what I would like.

Any suggestion for the correct formula in A1 in case column a contais
text
is welcome.

Thank you again for your help.
PS. I use ";" instead of "," since I am with European regional settings

:

I assume the 4 in A1 is the sum of B1 and C1?

Enter this formula in B1 and copy across to C1:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B10,ROW(B3:B10)-MIN(ROW(B3)),0,1)),--(ISERROR(B3:B10)))

A1: =B1+C1

--
Biff
Microsoft Excel MVP


I have the following 3 columns table starting at cell A2.
It is a simlified version since the real table is about 40x2000=8000
cells.

- A_B______C
1
2 a_b______c
3 1_#N/A___Ok
4 1_Ok_____Ok
5 3_Ok_____#N/A
6 4_#VALUE_#N/A
7 4_#VALUE_Ok
8 4_#N/A___Ok
9 4_Ok_____Ok

I want to get in row 1 the number of #Error codes per column (b and
c)
based
on column a filtered data. [If possible A1 should show filtered
value.]

To be clear:
In the above table I implement autofilter to the range A3:C9 and
after
having filter for value 4 in Column a,
I want to get as result [A1=4] B1=3, C1=1 (column b will have 2
times
#VALUE
+ 1 #N/A = 3 and column c will have 1 time #N/A)
If I filter column a for 1 the results will be [A1=1] B1=1, C1=0 and
if
I
filter for 3 the results will be [A1=3] B1=0 and C1=1.

I don't want to implement individual filters on columns b and c for
#Errors

Thank you in advance.
Ps. I am looking for a formula not VBA code

 

[gr8posts]

Excellent !!!
You are really good dear friend.
Thanks a lot.
It was a great help for me. )))

Try this array formula** :

Assume the filtered range is A3:A10

=INDEX(A3:A10,MATCH(1,(SUBTOTAL(3,OFFSET(A3:A10,ROW(A3:A10)-MIN(ROW(A3:A10)),0,1)))*(A3:A10<>""),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

"If the column was filtered on "yes" then you want "yes" returned to cell
A1?"

Exactly.
This is what I want if possible.
Thanks again.

Ok, in other words, you want cell A1 to display what column A is filtered
on?

If the column was filtered on "yes" then you want "yes" returned to cell
A1?

--
Biff
Microsoft Excel MVP


Great,
Thank you for your answer it works as I was expecting. (in cells B1 and
C1)

For cell A1 it is not the B1 + C1 but the filtered value in column a
What I did was to set A1=SUBTOTAL(1;A1:A10) (average of filtered
values)
and
this gives me the expected result in A1 BUT
ONLY if column a contains ONLY numbers AND
ONLY if a filter is active.

Ihope you understand what I mean:If there is no filter active it gives
the
Average of column A which is not suitable, and if column A has text it
gives
the error #DIV/0! which is normal but not what I would like.

Any suggestion for the correct formula in A1 in case column a contais
text
is welcome.

Thank you again for your help.
PS. I use ";" instead of "," since I am with European regional settings

:

I assume the 4 in A1 is the sum of B1 and C1?

Enter this formula in B1 and copy across to C1:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B10,ROW(B3:B10)-MIN(ROW(B3)),0,1)),--(ISERROR(B3:B10)))

A1: =B1+C1

--
Biff
Microsoft Excel MVP


I have the following 3 columns table starting at cell A2.
It is a simlified version since the real table is about 40x2000=8000
cells.

- A_B______C
1
2 a_b______c
3 1_#N/A___Ok
4 1_Ok_____Ok
5 3_Ok_____#N/A
6 4_#VALUE_#N/A
7 4_#VALUE_Ok
8 4_#N/A___Ok
9 4_Ok_____Ok

I want to get in row 1 the number of #Error codes per column (b and
c)
based
on column a filtered data. [If possible A1 should show filtered
value.]

To be clear:
In the above table I implement autofilter to the range A3:C9 and
after
having filter for value 4 in Column a,
I want to get as result [A1=4] B1=3, C1=1 (column b will have 2
times
#VALUE
+ 1 #N/A = 3 and column c will have 1 time #N/A)
If I filter column a for 1 the results will be [A1=1] B1=1, C1=0 and
if
I
filter for 3 the results will be [A1=3] B1=0 and C1=1.

I don't want to implement individual filters on columns b and c for
#Errors

Thank you in advance.
Ps. I am looking for a formula not VBA code

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Excellent !!!
You are really good dear friend.
Thanks a lot.
It was a great help for me. )))

Try this array formula** :

Assume the filtered range is A3:A10

=INDEX(A3:A10,MATCH(1,(SUBTOTAL(3,OFFSET(A3:A10,ROW(A3:A10)-MIN(ROW(A3:A10)),0,1)))*(A3:A10<>""),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

"If the column was filtered on "yes" then you want "yes" returned to
cell
A1?"

Exactly.
This is what I want if possible.
Thanks again.


:

Ok, in other words, you want cell A1 to display what column A is
filtered
on?

If the column was filtered on "yes" then you want "yes" returned to
cell
A1?

--
Biff
Microsoft Excel MVP


Great,
Thank you for your answer it works as I was expecting. (in cells B1
and
C1)

For cell A1 it is not the B1 + C1 but the filtered value in column a
What I did was to set A1=SUBTOTAL(1;A1:A10) (average of filtered
values)
and
this gives me the expected result in A1 BUT
ONLY if column a contains ONLY numbers AND
ONLY if a filter is active.

Ihope you understand what I mean:If there is no filter active it
gives
the
Average of column A which is not suitable, and if column A has text
it
gives
the error #DIV/0! which is normal but not what I would like.

Any suggestion for the correct formula in A1 in case column a
contais
text
is welcome.

Thank you again for your help.
PS. I use ";" instead of "," since I am with European regional
settings

:

I assume the 4 in A1 is the sum of B1 and C1?

Enter this formula in B1 and copy across to C1:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B3:B10,ROW(B3:B10)-MIN(ROW(B3)),0,1)),--(ISERROR(B3:B10)))

A1: =B1+C1

--
Biff
Microsoft Excel MVP


I have the following 3 columns table starting at cell A2.
It is a simlified version since the real table is about
40x2000=8000
cells.

- A_B______C
1
2 a_b______c
3 1_#N/A___Ok
4 1_Ok_____Ok
5 3_Ok_____#N/A
6 4_#VALUE_#N/A
7 4_#VALUE_Ok
8 4_#N/A___Ok
9 4_Ok_____Ok

I want to get in row 1 the number of #Error codes per column (b
and
c)
based
on column a filtered data. [If possible A1 should show filtered
value.]

To be clear:
In the above table I implement autofilter to the range A3:C9 and
after
having filter for value 4 in Column a,
I want to get as result [A1=4] B1=3, C1=1 (column b will have 2
times
#VALUE
+ 1 #N/A = 3 and column c will have 1 time #N/A)
If I filter column a for 1 the results will be [A1=1] B1=1, C1=0
and
if
I
filter for 3 the results will be [A1=3] B1=0 and C1=1.

I don't want to implement individual filters on columns b and c
for
#Errors

Thank you in advance.
Ps. I am looking for a formula not VBA code