IF COUNTIF & COUNTA on Filtered Visible Cells

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tinä (Tin.1f7u3z_at_excelforum-nospam.com)
Date: 11/04/04 

Date: Thu, 4 Nov 2004 15:27:59 -0600

Hi Aladin,

Hope this will help.

Correction to penultimate 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ä
------------------------------------------------------------------------
Tinä's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15410
View this thread: http://www.excelforum.com/showthread.php?threadid=274285

Relevant Pages

  • IF COUNTIF & COUNTA on Filtered Visible Cells
    ... Helper Column "U" calculates Row Number for Group Name Last Occurrence ... Expected Results "No Filter Applied": ... Expected Results "AutoFilter Applied": ... > also include the non-filtered data rather than just the Filtered ...
    (microsoft.public.excel.misc)
  • IF COUNTIF & COUNTA on Filtered Visible Cells
    ... Correction to Last Posting: ... Helper Column "U" calculates Row Number for Group Name Last ... Expected Results "No Filter Applied": ... Aladin Akyurek Wrote: ...
    (microsoft.public.excel.misc)
  • IF COUNTIF & COUNTA on Filtered Visible Cells
    ... Correction to Previous Posting: ... Helper Column "U" calculates Row Number for Group Name Last Occurrence ... Expected Results "No Filter Applied": ... The Formulae below works ok on non-filtered data but when I use ...
    (microsoft.public.excel.misc)
  • Re: Autofilter Macro
    ... If you arre going to use Stefi's suggestion of creating a helper column then ... the helper column becomes the criteria for the Advance filter. ... Actually I have used autofilter on other columns, so I think I'll stick to ... I hope to find a criterion ...
    (microsoft.public.excel.programming)
  • Re: Overcoming the 255 char limit using autofilter
    ... but if you're looking for a substring within that long string ... you could use a helper column with a formula ... Then you can filter on that column to show/hide True or False. ... > I am trying to autofilter a csv file that has over 255 char in a field. ...
    (microsoft.public.excel)