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
Bookmarks