Excel Misc >> IF COUNTIF & COUNTA on Filtered Visible Cells
Hi Frank,
Hope this will help.
Correction to Previous Posting:
My Helper Column "U" increments one Row at a time and says:
Check from Row above Current Row back to beginning of my "V" Range: i
the Room was used previously give me the Last (MAX) time it was used b
returning the relevant Row Number of the (text based) Group Nam
(Executive, Trainees, Manager, Graphics, etc.) that used the Room Last
The Helper Column "U" then passes this data to the Formula in Colum
"T"  it checks for the criteria within the specified Range and doe
the COUNT calculation using the Row above Current Row where the Grou
Name appears, if at all, back to the Last (MAX) Row where it appeared
subtracts Last Row Number from the Row above Current Row Number t
return Number of Times Group Name has not used Room.
NB. ++ used to align text under relevant columns
Row Number 10 is blank / empty.
Row Number 11 is the first Row with data.
Row10Row10 means Row Number 10 minus Row Number 10
Helper Column "U" calculates Row Number for Group Name Last Occurrence
Apart from Row 11 which is the first Row of data and returns 0 (zero
in Column "T" and 0 (zero) in Helper Column "U"; the following applie
thereafter:
A zero in Helper Column "U" is a first occurrence of the Group Name.
A zero in Column "T" means a consecutive double, triple or qua
occurrence of a Group Name using a Room.
Expected Results "No Filter Applied":
Row No.++Col "T" Results++Calculation Col "T"+Column "U" Helper++Colum
"V"
10++++++Blank++++++++Blank+++++++++Blank++++++++++ +Blank
11++++++0+++++++++++Row10Row10++++0++++++++++++++Executive
12++++++1+++++++++++Row11Row10++++0++++++++++++++Manager
13++++++2+++++++++++Row12Row10++++0++++++++++++++Trainees
14++++++0+++++++++++Row13Row13++++13+++++++++++++Trainees
15++++++0+++++++++++Row14Row14++++14+++++++++++++Trainees
16++++++3+++++++++++Row15Row12++++12+++++++++++++Manager
17++++++6+++++++++++Row16Row10++++0++++++++++++++Graphics
18++++++1+++++++++++Row17Row16++++16+++++++++++++Manager
19++++++3+++++++++++Row18Row15++++15+++++++++++++Trainees
20++++++8+++++++++++Row19Row11++++11+++++++++++++Executive
21++++++2+++++++++++Row20Row18++++18+++++++++++++Manager
Expected Results "AutoFilter Applied":
Row No.++Col "T" Results++Calculation Col "T"+Column "U" Helper++Colum
"V"
10++++++Blank++++++++Blank+++++++++Blank++++++++++ +Blank
12++++++1+++++++++++Row11Row10++++0++++++++++++++Manager
14++++++3+++++++++++Row13Row10++++0++++++++++++++Trainees
15++++++0+++++++++++Row14Row14++++14+++++++++++++Trainees
17++++++6+++++++++++Row16Row10++++0++++++++++++++Graphics
18++++++5+++++++++++Row17Row12++++12+++++++++++++Manager
20++++++9+++++++++++Row19Row10++++0++++++++++++++Executive
27++++++11++++++++++Row26Row15++++15+++++++++++++Trainees
28++++++0+++++++++++Row27Row27++++27+++++++++++++Trainees
31++++++20++++++++++Row30Row10++++0++++++++++++++Admin
36++++++17++++++++++Row35Row18++++18+++++++++++++Manager
38++++++20++++++++++Row37Row17++++17+++++++++++++Graphics
AutoFilter will return the correct Results if only one criteria i
Filtered using Column "V", eg: "Trainees". However, if I apply a Filte
from a different column where the Results will include a mix of Grou
Names the Results returned in Column "T" and Column "U" are calculate
using the "NonFiltered" data in the whole column and "not the Filtere
Visible Cells" as is required.
Is there a Formula that can return AutoFiltered multiple criteri
Results for Filtered Visible Cells only based on the above sample?
Thanks
Tinä
Quote:
Originally Posted by Tinä
Hi Frank,
The Formulae below tells me how frequently the ROOMS are being used b
different Groups of people and with the aide of the Formula in th
Helper Column how long they have not been used.
Corrected in above Posting <My Helper Column "U" increments one Row a
a time and says:
Check from Current Row back to beginning of my "V" Range: if the Roo
was used previously give me the Last (MAX) time it was used b
returning the relevant Row Number of the (text based) Group Nam
(Executive, Trainees, Manager, Graphics, etc.) that used the Room Last.
The Helper Column "U" then passes this data to the Formula in Column
"T"  it checks for the criteria within the specified Range and does
the COUNT calculation using the Current Row where the Group Name
appears, if at all, back to the Last (MAX) Row where it appeared:
subtracts Last Row Number from the Current Row Number. >
Apologies for any confusion.
The Formulae below works ok on nonfiltered data but when I use
Autofilter the results are not as they should be, because the results
also include the nonfiltered data rather than just the Filtered
Visible Cells.
Using the Formulae below, I can obtain the required data from the
Columns in a nonfiltered state.
Column T:
=IF(COUNTIF($V$10:$V10,$V11),COUNTA(INDIRECT("V"&U11+1&":V"&ROW()))1,COUNTA($V$10:$V10))
Column U (helper column):
=MAX(IF($V$10:$V10=$V11,ROW($V$10:$V10)))
Column V:
Text Data
Further help appreciated.
Thanks
Tinä

Tinä

Tinä's Profile: http://www.hidelink.com/ ;userid=15410
View this thread: http://www.hidelink.com/
Excel520
