T
Tinä
Hi Aladin,
Thank you for assistance. Unfortunately, the Formula is not retrieving
the correct results and also at the end of your Formula:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange)-MIN(ROW(Vrange)),,1)),--(Vrange="Rome"))
(Vrange="Rome")) I need to test for more than one text criteria for
which I have tried using an array but the results are still incorrect.
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"&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ä
Quote:
Originally Posted by Aladin Akyurek
If you're trying to count the occurrences of a certain text in V which
is part of an AutoFilter'ed range....
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange)-MIN(ROW(Vrange)),,1)),--(Vrange="Rome"))
would calculate the frequency of occurrence of "Rome" in Vrange, the
range in column V in the area subjected to AutoFilter.
Thank you for assistance. Unfortunately, the Formula is not retrieving
the correct results and also at the end of your Formula:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange)-MIN(ROW(Vrange)),,1)),--(Vrange="Rome"))
(Vrange="Rome")) I need to test for more than one text criteria for
which I have tried using an array but the results are still incorrect.
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"&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ä
Quote:
Originally Posted by Aladin Akyurek
If you're trying to count the occurrences of a certain text in V which
is part of an AutoFilter'ed range....
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange)-MIN(ROW(Vrange)),,1)),--(Vrange="Rome"))
would calculate the frequency of occurrence of "Rome" in Vrange, the
range in column V in the area subjected to AutoFilter.