If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IF COUNTIF & COUNTA on Filtered Visible Cells
Hi Frank, Hope this will help. Correction to Last 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: if the Room was used previously give me the Last (MAX) time it was used by returning the relevant Row Number of the (text based) Group Name (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 Row above 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 Row above Current Row Number to 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. Row10-Row10 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 applies 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 quad occurrence of a Group Name using a Room. Expected Results "No Filter Applied": Row No.++Col "T" Results++Calculation Col "T"+Column "U" Helper++Column "V" 10++++++Blank++++++++Blank+++++++++Blank++++++++++ +Blank 11++++++0+++++++++++Row10-Row10++++0++++++++++++++Executive 12++++++1+++++++++++Row11-Row10++++0++++++++++++++Manager 13++++++2+++++++++++Row12-Row10++++0++++++++++++++Trainees 14++++++0+++++++++++Row13-Row13++++13+++++++++++++Trainees 15++++++0+++++++++++Row14-Row14++++14+++++++++++++Trainees 16++++++3+++++++++++Row15-Row12++++12+++++++++++++Manager 17++++++6+++++++++++Row16-Row10++++0++++++++++++++Graphics 18++++++1+++++++++++Row17-Row16++++16+++++++++++++Manager 19++++++3+++++++++++Row18-Row15++++15+++++++++++++Trainees 20++++++8+++++++++++Row19-Row11++++11+++++++++++++Executive 21++++++2+++++++++++Row20-Row18++++18+++++++++++++Manager Expected Results "AutoFilter Applied": Row No.++Col "T" Results++Calculation Col "T"+Column "U" Helper++Column "V" 10++++++Blank++++++++Blank+++++++++Blank++++++++++ +Blank 12++++++1+++++++++++Row11-Row10++++0++++++++++++++Manager 14++++++3+++++++++++Row13-Row10++++0++++++++++++++Trainees 15++++++0+++++++++++Row14-Row14++++14+++++++++++++Trainees 17++++++6+++++++++++Row16-Row10++++0++++++++++++++Graphics 18++++++5+++++++++++Row17-Row12++++12+++++++++++++Manager 20++++++9+++++++++++Row19-Row10++++0++++++++++++++Executive 27++++++11++++++++++Row26-Row15++++15+++++++++++++Trainees 28++++++0+++++++++++Row27-Row27++++27+++++++++++++Trainees 31++++++20++++++++++Row30-Row10++++0++++++++++++++Admin 36++++++17++++++++++Row35-Row18++++18+++++++++++++Manager 38++++++20++++++++++Row37-Row17++++17+++++++++++++Graphics AutoFilter will return the correct Results if only one criteria is Filtered using Column "V", eg: "Trainees". However, if I apply a Filter from a different column where the Results will include a mix of Group Names the Results returned in Column "T" and Column "U" are calculated using the "Non-Filtered" data in the whole column and "not the Filtered Visible Cells" as is required. Is there a Formula that can return AutoFiltered multiple criteria Results for Filtered Visible Cells only based on the above sample? Thanks Tinä Tinä Wrote: Hi Frank, The Formulae below tells me how frequently the ROOMS are being used by different Groups of people and with the aide of the Formula in the Helper Column how long they have not been used. My Helper Column "U" increments one Row at a time and says: Check from Current Row back to beginning of my "V" Range: if the Room was used previously give me the Last (MAX) time it was used by returning the relevant Row Number of the (text based) Group Name (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 non-filtered data but when I use Autofilter the results are not as they should be, because the results also include the non-filtered data rather than just the Filtered Visible Cells. Using the Formulae below, I can obtain the required data from the Columns in a non-filtered state. Column T: =IF(COUNTIF($V$10:$V10,$V11),COUNTA(INDIRECT("V"&U 11+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.excelforum.com/member.php...o&userid=15410 View this thread: http://www.excelforum.com/showthread...hreadid=274285 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How can I set the Print area for visible cells | A.N. Syed | Worksheet Functions | 1 | September 8th, 2004 06:32 PM |
Copy and paste only visible cells under auto-filter | Phil | Worksheet Functions | 2 | August 25th, 2004 07:20 PM |
Selecting a cell based on counting visible cells from the top | Alan | General Discussion | 1 | June 26th, 2004 12:53 AM |