# Autofilter on more than one sheet

1. ## Autofilter on more than one sheet

I got two questions regarding Filter -> Autofilter, but first a little about my workbook:

I got a huge Excel workbook with several sheets (7). I got over 250 000 rows of data that I
have divided in to 6 sheets (called Data 1, Data 2...), then I got another sheet named "Resultat"
(Norwegian for result) where I fill inn all the criteria for calculations.

The workbook consists on some values (measured on 30 positions at each object)

I then use a SUMPRODUCT function in an IF function, based on the criteria I set in the sheet "Resultat"
the two criteria I use is >,>=,=,=<,< an a value 0-7. My function then count the cells that match my
desired criteria and list them up, based on their position (1-30) in each sheet, then I add all the "counted cells"
together (listed up on positions) in "Resultat.

My IF/SUMPRODUCT formula is:
=IF(Resultat!\$A\$3="<";SUMPRODUCT((\$C\$3:\$C\$63682<Resultat!\$B\$3)*(\$E\$3:\$E\$63682=G6));
IF(Resultat!\$A\$3="<=";SUMPRODUCT((\$C\$3:\$C\$63682<=Resultat!\$B\$3)*(\$E\$3:\$E\$63682=G6));
IF(Resultat!\$A\$3="=";SUMPRODUCT((\$C\$3:\$C\$63682=Resultat!\$B\$3)*(\$E\$3:\$E\$63682=G6));
IF(Resultat!\$A\$3=">=";SUMPRODUCT((\$C\$3:\$C\$63682>=Resultat!\$B\$3)*(\$E\$3:\$E\$63682=G6));
SUMPRODUCT((\$C\$3:\$C\$63682>Resultat!\$B\$3)*(\$E\$3:\$E\$63682=G6)) ))))

Cell "A3" is where I fill inn ">,>=,=,=<,<"
Cell "B3" is where I sett the value form 0-7

My sheet set up:

_______A___________B__________C___________D_____________E___
1 Date measured |Object |Measurement 1 |Measurement 2 |Position
2......................|............|.....................|.......................|.........
3......................|............|.....................|.......................|.........

Question 1:
When I am using the "Autofilter" option to only list objects that starts with "A" (an hide all who does not),
the values get hidden, but my calculations does not exclude the values that does not meet the criteria.
Is it an easy way to fix that (I have read in the forum about the SUBTOTAL function, but I do not think that
would help me)

Question 2:
Is it possible to in any way "centralize" the Auto filter options in sheet "Resultat" (so I just need to select the filtering
there and not in each data sheet?

Hope I did not scare you off with all the writing:D

2. Good afternoon crazyleg

...and welcome to the forum!!

Originally Posted by crazyleg
Question 1:
but my calculations does not exclude the values that does not meet the criteria.
No, Subtotal won't help you in this instance. Your best bet would be to use a helper column (which can be hidden if you like) containing a custom function that will change it's value if the row is hidden - which is essentially what happens when a column is filtered. The function below will return 1 if the row is visible, 0 if it is hidden. Then work an extra condition into your SumProduct formula, to only total rows that contain a 1.

``Please Login or Register  to view this content.``
Once you've put the code in, use the function :
=HiddenRow(A2)
assuming, A2 is the first row of the data you want to filter, and copy down.
Of course, you won't be able to see tha value change, but trust me, it does...

Not sure what you want for question 2 - can you clarify?

HTH

DominicB

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1