IF COUNTIF & COUNTA on Filtered Visible Cells #7

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: 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.
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 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+++++++++++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++Colum
"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 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 "Non-Filtered" 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�


Tin� Wrote:
> 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. 
> 
> 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 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 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�


-- 
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

0
11/3/2004 8:43:32 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
513 Views

Similar Articles

[PageSpeed] 8

Reply:

Similar Artilces:

In macros, an instruction that returns the position of the Active Cell
In a macro, how can I make it return the position of the Active Cell Colum & Line. Or how can I set that position as a Condition; e.g. �If ActiveCell i B7 Then�.� Or �If ActiveCell belongs to Column G Then�.� Thanks in advance for any help. AC -- ac ----------------------------------------------------------------------- aca's Profile: http://www.msusenet.com/member.php?userid=385 View this thread: http://www.msusenet.com/t-187368645 Hi Sub test() Dim ws1 As Worksheet Set ws1 = Workbooks("Tester.xls").Sheets("Sheet2") With ws1 MsgBox ("Cell address is &qu...

a cell contains both text and number
how do i make a calculation if a cell has text and a number. Example the cell says "3 weeks" I want to calculate 3 weeks to read in the next column as "120 hours" =LEFT(A1,1)*1*40&" hours" Vaya con Dios, Chuck, CABGx3 "text and formulas" <text and formulas@discussions.microsoft.com> wrote in message news:D8C17645-DAFF-455A-B144-5B936B97ADCE@microsoft.com... > how do i make a calculation if a cell has text and a number. Example the cell > says "3 weeks" I want to calculate 3 weeks to read in the next column as "120 &g...

How Can I Make The Cursor go to the last letter of the cell?
I have a paragraph in the cell and I want to know how to make the cursor go to the end of the paragraph without clicking on it or using sendkey {f2}{end}{right} Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ anyone? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ I do not think this is possible without substantial coding. Although i...

Filter and Query Records Mismatch
When I perform a filter on a table I get 371 records but when I design a query based on the same criteria, I get 63 records. And the result in my report is not showing the correct data based on this. I can't figure out what I am doing wrong. Any help will be greatly appreciated. Thanks. ...

VB- If first cell with formula is blank, all cells in column returns blank.
Hi all, I am using ADO to connect to an excel sheet and display the data in vb form. In excel there is a column named "TAT" which has a formula t add two othe cell values to it. If any of the two cells is blank the the TAT col remains blank. If the very first cell in the TAT column has some value then VB showa all cell values in that column. But if the first cell is empty then al cells in tat column are shown blank, even if there are values in othe cells. What's the solution for this? Thank -- Message posted from http://www.ExcelForum.com Hi Of course you can try with some...

Cell Values
I would like to return the value of another cell(C1) if a cell has a value in it, otherwise I want it to return a value of 0. Example: IF(A1="any value",C1,0) This formula would be written in B1. "Any value" would be numbers or text. Thanks 4 your help!!! Try: =IF(A1<>"",C1,0) or =IF(ISBLANK(A1),0,C1) Good Luck, Mark Graesser "GaryW" <gary.wicker@acadiapolymers.com> wrote in message news:040b01c34721$41fe8d00$a301280a@phx.gbl... > I would like to return the value of another cell(C1) if a > cell has a value in it, otherwise I...

Having Formatting Remain on "Called" cell using IF function
I'm using an IF function to call some other cells, but I want to have the various words color coded, eg ("Certified" is green, "Recertify" is yellow, and "Not Certified" is red). I have the IF function working correctly, but when it "calls" the cell to have it display the appropriate word, the color of each word is not brought along with it...only the text. Is there a way to be able to color code it so that the appropriate color appears? Thanks! -- Zaraf ------------------------------------------------------------------------ Zaraf's Profi...

re: printer driver install on Windows 7 x64
I managed to get self-extracting exe to invoke the "Add Printer" wizard by starting a 64-bit executable that calls rundll32 and passes the "printui.dll,PrintUIEntry" command line. Unfortunately, after the driver is installed, the Windows "Program Compatibility Assistant" pops up a dialog box that says "The program might not have installed correctly". I did some research on MSDN regarding the PCA (Program compatibility assistant) and I quote: "The best option to exclude a program from PCA is to include, with the program, an applicatio...

Some cells do not recognize email address
Some of the ceels in my worksheet recognize email addresses so I can click on them and send an email. Other cells do not. the format is right. How do a get a cell to recognize an email address? Not knowing exactly what you have in those cells, expect they are complete email addresses, what version of Excel, and your options would suggest the following: If you have Excel 2002 (Excel XL) or higher: you can suppress hyperlink generation under the Auto Correct from the Tools menu. Tools (menu), Autocorrect Options, Autoformat as you type (Tab). so you want to do the opposite. Next...

Windows 7
I installed Money Deluxe 2008 on my Windows 7 RC1 machine. When I launch it, I see the splash screen for a second, then it's gone and it won't start. Any thoughts? Works great with Windows 7 for me. Did you reboot? If that doesn't work, reinstall Money. "Scott Friedman" <scott.friedman@bluecoat.com> wrote in message news:C652905A.B180%scott.friedman@bluecoat.com... > I installed Money Deluxe 2008 on my Windows 7 RC1 machine. > When I launch it, I see the splash screen for a second, then it's gone and > it won't start. > > Any thoughts?...

Offline Address Book #7
Hey, I have a pure Exchange 2003 (SP1) organization, with Windows 2003 and Outlook 2003. I have built a new Exchange 2003 server in a new office. This server is also a domain controller and GC. This server is the only DC/GC in this site. I have created new mailboxes, and there are no issues with mail flow, it all works. However, the new Exchange server doesn't have the Offline Address Book Public Folders. I have manually tried to push these public folder > Send the contents of the Offline Address Book, and it doesn't make any difference. No public folder data is being replic...

Text to columns #7
I have a worksheet that has the data in rows but I need them in columns. Is there a way that I can get the data to columns? ( example Rows 1,2,3 need to be Row 1 columns A, B C - Rows 4,5,6 need to be Row 2 columns A,B, C etc) hi, If i understand you correctly, i think you want to use the paste special - transposed. On the toolbar go Edit>PasteSpecial. in the dialog box(near the bottom on mine), check Transposed. This will paste data in rows into columns. Good luck FSt1 "jcross" wrote: > I have a worksheet that has the data in rows but I need them in columns. Is...

Calculate to another cell as default value
Hi. Given the following: A B C 1 5 9 __ 2 8 10 __ I'd like for the user to enter values in A and B and have Excel display B minus A in C as a DEFAULT value. That is, for row 1, once the user enters 9 in B, I want to display 4 in C but give the user the option to override the calculation. A formula in C won't work because the user would delete the formula if he overrides it. Thanx. I don't Why not use an extra column (C) and have this formula in D: =IF(C1="",B1-A1,C1) -- Kind regards, Niek Otten Microsoft MVP - Excel "...

Last cell in new formula
Can anyone please help? The code: Sub test() Dim x As Range Worksheets("sheet1").Activate Set x = Cells(Rows.Count, "N").End(xlUp) MsgBox x.Address End Sub I would like to find the last cell in column N and instead of the result being displayed in A msgbox, I would like to use the cell address in a formula after the code above e.g.: range("b2").formula = _ "sumproduct((N8:LASTCELLADDRESS>=0)*(N8:LASTCELLADDRESS<=1000)) Hi, Try this Dim LASTCELLADDRESS As Long LASTCELLADDRESS = Cells(Cells.Rows.Count, "...

concatenate cells from a column and paste to a cell
I'm trying to write a macro that will loop through a column and copy the data to a single concatenated string which will be pasted to another cell. The macro should step through the column of data until it reaches the end of the data set or an empty cell. I could name a range if needed. Any help getting me started???? The concatenate( ) function won't work with ranges..., so I assume I'll have to write a macro to loop. This will do it, but you may not be happy with the results since the resulting text string may be longer than Excel allows. This code runs from the cel...

Splitting Data in a cell, left or right of a separator.
I need to split some data in a cell to the right of a separator, which is a colon. eg: "CONSUMABLE:HPC92298X" Would someone be able to point me in the right direction.. many thanks, Ross One way is to use Data > Text to Columns Select the column Click Data > Text to columns (Delimited) Click Next In step 2: Type a colon in the "Other:" box Click Finish -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "ross" <ross_anderson57@hotmail.com> wrote in message news:01AD4975-1539-411E-B5D8-04B569268783@microsoft.c...

In Excel is it possible to hide a row condtional upon a cell val..
In Excel is it possible to hide a row condtional upon a cell value? with a macro yes,or you could format white text and all values would disapear. -- paul remove nospam for email addy! "Louise" wrote: > In Excel is it possible to hide a row condtional upon a cell value? The white text makes the text disappear, but not the entire row or column of cells. With a macro, is it possible to make the cells automatically unhide then if the data becomes relevant again due to changes? "paul" wrote: > with a macro yes,or you could format white text and all values woul...

intelligent message filter moving everything to junk mail
i recently upgraded to exchange 2003 on a windows 2000 server and installed the intelligent message filter. no matter what i set the scl rating threshold for gateway blocking or store junk email configuration (in global settings/message delivery), every message that is sent from an external source gets automatically dumped into the junk e-mail folder in outlook. but when a message is sent from the same exchange box to another user on the same exchange, its fine. i also installed a utility that views the archived messages, but that utility shows a blank SCL rating. maybe thats why its ending ...

after apply filter show blank form.How to detect 0 records??
hi I Have a form , and the user goes to filter by form and then apply filter. When no rows are returned a blank form is displayed how do I detect this and display a message 'no rows found'? mike Mike, I would like to say add a piece of line to your code... DCount("SomeField","SomeTable") < 0 Then MsgBox Esle run your filter but you didn't post any code. Perhaps that little tidbit I posted is enough to get you started? -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!&quo...

SUM Divided by COUNTIF w/o zeroes
I have a named array with 52 non-contiguous cells in it which I would like to SUM and then divide by the number of cells in the array that are not zeroes (they get filled in once a week and I don't want the zero-cells to skew the result). I have tried =SUM(named array)/COUNTIF(named array,"<>0") and about a bazillion variations which all seem logical but do not work. The SUM function component does work, but the COUNTIF function seems to be treating the named array as a single cell rather than analyzing the 52 components for the non-zero cells. Anyone hav...

date and time in 2 cells to create subtraction in 3rd
I have 2 cells that are in the format line of A1: 27/06/2006 9:42:00 pm B1: 7/06/2006 9:52:05 pm Want third column C1: 00:10 I wish to subtract the two cells to get the difference in time i.e 10:00 (only interested in nearest minute) Then a average at the end of the column of the the times in this 3rd column as a result of the difference between the 2 times. How would I do this? I could do it if it was just numbers but once it is in this time/dtae format I am completely lost! Thanks MIke Hi Mike, =MOD(B1,1)-MOD(A1,1) Format as Time You can use the AVERAGE function on the ...

Cell Format #7
Hello, I have a column where I would like to format as ##-##-##- ##-##-##. I went to Format > Cells and format it in Custom category. It works fine when I enter values, but when the data starts with a text, it won't work. Any suggestions. When I entered 123456789123, it converts to 12-34-56-78- 91-23. When I entered AU1234567890, it doesn't do anything. I want it to covert to AU-12-34-56-78-90. Any ideas would be helpful. You can only format numbers that way, you could custom format as "AU"-##-##-##-##-## and just enter the numbers 1234567890 -- Regards, ...

Adjacent cell belong to same merged cell?
How do I compare two adjacent cells to determine if they belong to the same merged cell? Adjacent cells cannot be merged cells. If two or more cells are merged, they become one cell. That is what "merge" means. Perhaps there is another way to phrase the question? Gord Dibben Excel MVP On Fri, 25 Mar 2005 11:11:02 -0800, DrKilbert <DrKilbert@discussions.microsoft.com> wrote: >How do I compare two adjacent cells to determine if they belong to the same >merged cell? Thanks Gord for getting back to me on this. Let me explain in more detail. I have a worksheet t...

locking data/cells so re-sorting a column doesn't re-position my d
I need to re-sort a column, but I want all the row's data associated with that row to stay with the SORT results. -- mtn_leisure mtn_leisure Wrote: > I need to re-sort a column, but I want all the row's data associated > with > that row to stay with the SORT results. > -- > mtn_leisure Try selecting the data in the other columns aswell and then sort the data should then stay together Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&...

New Computer Window 7
Where is the send and receive option? It's it not available on window live. I really miss that option. "joyce" <joyce@discussions.microsoft.com> wrote in message news:F80EEC6C-F30C-4EA8-B2E4-534CEDF23606@microsoft.com... > Where is the send and receive option? It's it not available on window > live. > I really miss that option. It's called "Synchronize" AFAIK. The easiest option is to press F5. When looking at Mail - F5 will send/receive all mail (synchronize). When in Newsgroups - F5 will sync all newsgroups. Colin Bro...