IF COUNTIF & COUNTA on Filtered Visible Cells #4

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.


-- 
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/2/2004 5:35:40 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
718 Views

Similar Articles

[PageSpeed] 16

Reply:

Similar Artilces:

100% CPU by using filter in Excel
An employee tried to use filter in one special table and the PC did nee 100% CPU time and work more than 15 min. I checked it with other PC. The table is working and PC use only abou 50% CPU and need about 3 min to finish the job. I installed the last SP3 for office XP and it is not solved th problem. The employee has German OS and english office. Has anyone an idea? Please help -- languate ----------------------------------------------------------------------- languatec's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2654 View this thread: http://www.excelforum...

If any cell in a range is blank question
Hello. I have two sheets in a workbook. One is a Summary sheet, and the next is titled "Bldg 5". What I am trying to make sure is that the range A1:K20 on the Bldg 5 sheet has had data entered to them (ie, they are not blank). The question that corresponds to Summary sheet A1 is "Has the data for Bldg 5 been updated?" If ANY blank in Bldg 5's range A1: K20, then put a "NO" in Summary A1's cell. If ALL of the Bldg 5 range had data in it, then put a "YES" in Summary A1. I also need A1 to update as data is entered into the Bldg 5 range (a...

Splitting a cell that has word wrap as the delimiter
Hi - I have an excel cell that has name on one line, then alt+enter, address on the next line, then alt+enter, state on the next line, etc... I want to split them - I tried using Data Text to Columns, but I can't figure out to use the Alt+enter as the delimiter. Any ideas? Check other and place the cursor in the box, hold down Alt while typing 010 on the numpad, release the Alt key -- Regards, Peo Sjoblom "Leslie" <anonymous@discussions.microsoft.com> wrote in message news:074301c3c3f9$59b12340$a001280a@phx.gbl... > Hi - > > I have an excel cell that ...

Changing color of a range of cells dending up on a value in anothr
Hi, I need to change the color of a range of cell, depending up on the value in another cell. I am using Excel 2003. And it allows me to give only 3 conditions when using conditional format. But I have 7 differnt conditions. Thanks in advance for any help. Srajes. Unless you move to XL2007, you will need to use VBA. Here are some sites that will help http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm http://www.mvps.org/dmcritchie/excel/condfmt.htm Alternatively, there is an add-in here http://www.xldynamic.com/source/xld.CFPlus.Download.html -- Steve "Srajes&qu...

Pivot Table question #4
Hi I have spreadsheets that take data via a pivot table from an OLAP cube that is held on a terminal server. The spreadsheets hold figures entered by me and figures obtain from the pivot table. I want to enable the automatic update on the pivot table but the pivot table is sorted by a project number obtained from the OLAP cube. My problem is if a new project is added to the OLAP cube then it is automatically selected from the drop down list. I want to be able to turn this off so that it only selects the project I have previously chosed. For example if I have selected the filter nu...

Text Box Linked To Cell
My goal is to have a text box that displays the contents of a particular cell. How do I link the text box to the cell? Thanks, Denise Hi denise, Make a Text Box from the drawing toolbar. Select the box and in the Formula Bar, type = and then select the cell, press enter. ( IE: =$C$5 ) The cell can also be on a different sheet, just do the same. ( IE: =Sheet2!C5 ) Dave denise Wrote: > My goal is to have a text box that displays the contents of a > particular > cell. How do I link the text box to the cell? > > Thanks, > Denise -- Piranha --------------------------...

connecting to data access page with filter criteria
I have an access database that I connect to from a GIS application (ArcView) using VBscript. I need to port my GIS project to the free viewer (ArcReader) which does not support VBscript but does support hyperlinking to a URL based on a value supplied by an underlying record. In my current project I can send filter critera to open a particular database, form and record. Would it be possible to do the same using data access pages or would I need to create a separate page for each record I want linked? ...

Truncated Merged Cells
Actually, I have truly MERGED cells that contain LINKS to other workbooks. The merged cells were created exactly as you described and the data within them (text) comes from several other workbooks, with the appropriate references. We created them in 95 and later converted them to 97. We can use them in Excel XP but they lose the data that would be displayed on 2002 and 2003. Have not tried 2000 yet. Thanks for the help. We are very concerned about this problem. Our workbooks are complex with many links. Elsa I only have the ideas that I already posted. And you can find posts in goog...

Is there a way to Sort Data by font color or cell shading?
I have a long list of items that I sort by either item number or date depending on what I'm looking for. From time to time these items status will change and I will gray them out by just shading the background of the cell gray. Is there a way I can sort these items by the background/shading of the cell first and then the secondary sort be date? So all the cells which contain gray shading will be first in the list (sorted by date if possible) then the rest of the list without the gray shading will follow by date as well. Thanks. Take a look at this link: http://www.cpearson.c...

Cell identity when printing comments on separate page
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When printing comments at end of page, it identifies the cell by letters and numbers. (i.e. Cell: AA33) Afterward the comment is shown for that cell. <br><br>Question: Instead of the cell identity being shown this way, is it possible ithe cell can also be identified by its X axis header for that column with the y access header for that Row. <br><br>Thank for your assistance. <br><br>Carl Sorry Carl, but I'm not clear on what you're asking for... In 'MySpeak' ...

CRM 4 workflow picklists
We have been creating workflows in CRM 4 to create an opportunity when a field is changed on a lead. In general this has not been an issue (and much easier then in CRM 3!) expect I have an issue with custom picklists. I have a pick list on the lead, and a picklist on the opportunity with the same values. Using the create entity step in the workflow manager I can not set the picklist value on the opportunity to the value on the lead. When trying to set a picklist it only lets me use a dynamic value of an identical picklist. Is there anyway I can tell the system that the picklist on the L...

ASP.Net 4 TreeView Collapses on Node Click
I have an ASP.Net 4 TreeView that's being populated by a SQLSiteMapProvider and sits on a MasterPage. When I click a node the tree collapses. EnableViewState is set to True ViewStateMode is set to Enabled Am I missing something? How do I keep the tree state as it is when I click a link? "Dave" <Dave.Burkett@Jacobs.com> wrote in message news:2038c0ba-5328-4138-b615-777e02975478@c7g2000vbc.googlegroups.com... > Am I missing something? How do I keep the tree state as it is when I > click a link? This "feature" has always been there and I...

Assigning a color to a cell
I saw some other post about kind of the same problem but not this way. I have a risk evaluation formula in a cell. Output range is between 1 and 9 If between 1 and 3, I want to assign the color lightgreen to it If between 4 and 6 I want to assign the color yellow to it and red if 7 to 9. Is this possible within the Function use ? If not, How can I apply that automatically ? a macro ? How ? TIA Hi See menu Format > Conditional format. It has a very helpful wizard. (Remember to consider what should happen at exact numbers 3, 6, 9.) HTH. Best wishes Harald "Junkyard Engineer&qu...

Inserting an image into a cell
Is there any way to insert an image (or a series of images) into a cell rather than onto a worksheet? Many thanks hi unless they have changed something in 2007, about the only way you can do that is insert it as a comment. http://www.contextures.com/xlcomments02.html#Picture Regards FSt1 "Trent SC" wrote: > Is there any way to insert an image (or a series of images) into a cell > rather than onto a worksheet? > > Many thanks > > > Hi, Not into the cell but maybe you could use the comment to hold your image. http://www.contextures.com/xlcomments0...

Running macros for different cells at the same time
Hi all, Is it possible to run different macros on different cells at the sam time? If so how do I go about setting it up? Cheers Michae -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to do. Could you give an example? -- Regards Frank Kabel Frankfurt, Germany > Hi all, > > Is it possible to run different macros on different cells at the same > time? If so how do I go about setting it up? > > Cheers > > Michael > > > --- > Message posted from http://www.ExcelForum.com/ Yes it is possible to run different m...

NewsMaestro Usenet Supertool v. 4.4.8 for Windows/Linux/Unix released
Download page: http://rapidshare.com/files/118192521/NewsMaestro_4.4.8_Src.zip (Hit Free button to download. For some download managers you need to open the .html file it downloaded to do actual download.) * Documentation update. * Bug fix in Archive 2 HTML generator while doing ReChapter. News Maestro is a power tool for usenet that automatically performs any standard Usenet operation, as specified in various RFCs, individually, or in bulk, on any number of groups and articles, utilizing sophisticated filters to select the articles to be processed. As an automated tool to do anything you...

Word Wrap in Cells
Hiya, Have a strange one. Have word wrap set to on in cell format and have text in paragraphs (using alt-ret) within the cell. Some sentences (all within the same cell)are wrapped but others aren't. Any ideas? New one on me this. Many thanks, Shaun Hi if you have lots of text in your cell (>1000 characters) this could happen -- Regards Frank Kabel Frankfurt, Germany "Shawk" <shawk@clara.co.uk.3guesses> schrieb im Newsbeitrag news:1103641104.32476.1@demeter.uk.clara.net... > Hiya, > > Have a strange one. Have word wrap set to on in cell format and ha...

Filter Date
Hi, I have a form which contains a listbox (holding the values Process Date and Quantity) and I have a textbox which displayed the current month. What I want to do is when I click on a command button (cmdMonthDown) and the textbox value changes from May to April I want the values in the listbox to be filtered so that only those values are disiplayed that were entered in April. If I'm not making sense please let me know otherwise your help would be appreciated. Thanks Reference the text box as a parameter in the list box's RowSourcee, e.g. if the text box shows the month name in fu...

Splitting multiple cell contents from 1st column into 4 columns
n my first column I have 4 cells (1-4) (5-8) etc. with general content. The content from each of these 4 cells needs to be placed in a separate column to make a list that I can sort etc. Example: COLUMN A 1 01-04-425-001 2 Heatherridge Road #301 3 Harry Smith 4 H25 Condo Fairfax Place 5 01-05-356-041 6 McGrath Street # 56 7 Mary Jones 8 B45 Condo Lemon Circle . . . . . . and so on and so on every 4 cells. I have hundreds of 4 cell descriptions. I just can't do this one by one. Can anyone help, PLEASE, PLEASE. I am really a novice at Excel though I use it for e...

MFC #4
How to work in MFC without using Wizard ...

dropdown filter on Data Access Page
Hi, I have a data access page that is based on a query in my database. Currently the page brings up all data for today, and the page is formated into teams So it looks like this Today's date Team #1 entry #1 entry #2 Team #2 entry #1 Since this page can get long sometimes, I would like to add a dropdown menu at the top of the page, that allows them to filter their results by team. So once they make a selection they would only see the team selected. Today's date Team (only 1 listed) Entry #1 Entry #2 I've been trying to search on this, but jus...

how read value from last selected cell? It is possible? how get adress last selected cell?
for example. I write to cell A11 value "hello", and next: IF I click "ENTER" or IF I click mouse in any different cell (A15, B10, C1... free choice) I wont to display: MsgBox "Value in your last selected cell = ???" On this example MsgBox "Value in your last selected cell = hello" it is very important for me. I try with event of Private Sub Worksheet_Change(ByVal Target As Range) ?? but :( thanks everybody, Andrzej Andrzej You have little choice but to use the Worksheet_Change() event as the Worksheet_SelectionChange() event, give you the cell ...

How to send attachment to a filtered email list of people
Surely there must be a way to add an attachment to an email template? Do you have to use the email template when sending direct email through CRM? Whenever I do an advanced find and want to do an email to the group, the only choice is email templates that don't seem to have any attachment options. Someone please tell me they have a workaround for this. Much appreciated!! There may be a way, using workflow Create a new, manually invoked workflow rule that generates direct email. Don't use the template option. Then you can select an attachment. (See the unsupported "slug...

How do I pass series data x-value range to a chart from a cell?
I have a series on a line chart (x-values "=Sheet1!$A$1:$A$10"). I also have two worksheet cells (B1 & B2) that contain the start and end date for the x-axis of the chart. I want to be able to link the chart series data to the start and end date values so that the chart automatically adjusts when I change the dates in the worksheet cells. I can create a string or address for the x-values but how do I do not know how to pass this to the chart series?? I know I can do this using a macro, but I want to do it without macros so that friends who use MicrosoftCharts (the cheap, n...

Passing Complex filter via URL
Hi Everyone, Ok, I'm baffle on this one and it may be that I’m trying to do it the “Crystal” way  I have many reports in which I pass parameters via the url. This works great. Now I need to pass complex statements that I’ll like to add/append to the Where command via URL. i.e. http://dummy.com/reportserver/misc/ReportServer/Pages/ReportViewer.aspx?rpAddToWhere=id>10 and level=urgent and table.field=(select field3 from x where sample=20)&/HelpDesk/HDTicketsDetail Any ideas on how I can achieve this? BTW, I'm using MSSQL 2005. Thanks in advance. BTW...