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

Similar Articles

[PageSpeed] 26

Reply:

Similar Artilces:

Text Disappears from Controls after 4 Hours
Hi: I have a vc++ (vc.net 2003) dialog-based application that reads a 129 byte burst of data from the serial port every second and displays the received information every 5 seconds in a static text box, a couple of list boxes and a number of progress bars. The data is read in a separate thread and the thread posts a message to the main dialog on the 1st and 5th seconds. On the 1st second the static text box is changed and on the 5th second the list boxes and progress bars are updated. My problem is that after the application runs about 4 hours the static text box disappears. Then, if I mi...

Filters, Buttons, Queries, oh my!
i have a main form with a subform. the main form and the subform are unrelated. the subform is based on a query that lists a load of stuff and does some calcs that use numbers on the main form with numbers from the subform. this all works great. i have some buttons that allow me to cause a filter to be applied to the subform based on the code that i put in the buttons on click event. there are 6 buttons that turn on a specific filter and 6 other buttons that turn off the filter. i know i probably should have used toggle buttons, but as i am still learning i wanted to make it easy on my...

How to line up the last cell in each row?
I have some data for which I am only interested in the final column from each row. The problem is that each row is a different length, so that row 1 may be 10 columns wide, row 2 15 columns, etc. How can I get the last element from each row to line up in the same column so that I can sort, sum, etc.? Here is one solution: Insert a new column at A1 so you now have shifted all of your data to the right. Assuming that Row 1 has headers your first data record will be in row 2. Type this formula into A2 =OFFSET(B2:IV2,0,COUNTA(B2:IV2)-1,1,1) Drag copy down to the end of your data. Column ...

macro to check if cells have values in them...then
Hi - I am looking for a macro that will check 2 things....then do something. The first is if a range of cell values in are blank. lets say Sheets("Sheet1").Range("D6:G48"). Next is if a particular cell contains a given value , Sheets("sheet1").Range("E5") should equal "Year 1" if true. If both these are true then I want the macro to copy a range of cells from Sheets("Sheet2").Range("H6:H48") to Sheets("Sheet1").Range("D6:D48"). I tried to use IsEmpty but it seems to return a "False" if a...

Microsoft Publisher Greeting Card Contest 2004 #4
Yes, it is that time! Put your best ideas together and create a greeting card with Microsoft Publisher for your chance to win a copy of Microsoft Office 2003 Professional! For more details, check out the following link: http://www.publishermvps.com/Default.aspx?tabid=103 Submit your entries to: contest@publishermvps.com. All entries must be submitted by December 17, 2004. All entries will be converted to Publisher 98 format by me before uploading so that more users may be able to open the files. I will also retain the original file format offline. If you have any questions, please let us kn...

CountIf Statement
Can anybody help? I am creating a formula in a cell of a spreadsheet which will Count all in stances of "distribution centre" within a column This is achieved using the =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") I was wondering whether it was possible in excel to use a statemen like =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") and (nex expression) any ideas -- Message posted from http://www.ExcelForum.com You can use sumproduct to count with more than one condition multiple ranges =SUMPRODUCT(--(Range1="GROUP&...

Links #4
Hi Is it poss to link to a sheet in a different excel workbook even if th workbook is closed? Or could you have a workbook that is open but in the background & hid (a master workbook) and both of the (2) are then linked to tha workbook But this would mean that it needs to open the master 1 when you clic on of one other workbooks (1 master 2 different workbooks but need sam data) But then again if 1 was open + the master then you went and opened th other you would get a message about file already open????? Thank -- ra -----------------------------------------------------------------...

formula in the same cell?
I have a value in a cell (B5)$3.39. I would like to add 10% to it. How do I do that? and than still be able to copy the formula to 100 other cells? I can get it by 3.39*10%+3.39 but I would like to be able to just type in one formula and copy it to the rest. The other 99 cells all have different numbers. Please help Nycole, Your first value is in B5 so in C5 put =B5*110% Drag down Dan E "Nycole" <nborget@federated-group.com> wrote in message news:e17b38a8.0309120938.42a859cb@posting.google.com... > I have a value in a cell (B5)$3.39. I would like to add 10% to it. ...

Filters
When I click on a column filter button and select "sort A to Z" the filter button picture changes to show an up or down arrow indicating that this column was sorted ascending or decending. I am using Excell 2007; I'm not sure this applies to previous versions. Is there a way to interrogate the filter or column to determine if the column filter is showing the up or down arrow indicating that it is sorted. Thanks for any help Fred ...

Wave 4: Import from Outlook
Has the ability to import messages etc. from Outlook been restored? -- Noel No -- Ron Sommer MS MVP- Windows Live Mail "Ildhund" wrote in message news:D7C73049-450C-4C43-8046-FB925F1AC816@microsoft.com... Has the ability to import messages etc. from Outlook been restored? -- Noel Nope... -- ....winston MS-MVP[Mail] -- "Ildhund" wrote in message = news:D7C73049-450C-4C43-8046-FB925F1AC816@microsoft.com... Has the ability to import messages etc. from Outlook been restored? --=20 Noel=20 ...

Rounding Seconds in 4/21/2010 4:30:23 PM
I am trying to round the seconds portion of a column of cells containing both date and time. Currently the cells contains: 4/21/2010 4:30:23 PM 4/21/2010 4:30:45 PM I'd like the cell to contain: 4/21/2010 4:30:00 PM 4/21/2010 4:31:00 PM I couldn't figure out how to get ROUND or REPLACE to do this. Thanks for the help. =MROUND(A1,TIME(0,1,0)) If you have problems, read the Excel help for the MROUND function. -- David Biddulph "Tom Langley" <TomLangley@discussions.microsoft.com> wrote in message news:4396E037-F1A9-40E8-8A62-B7BAD372B248@micro...

To safety merge cells without data destroyed, and smart unmerge!
Please download and get a trial on AddinTools Assist from http://www.addintools.com If you merge some cells in Excel, just the data of first cell could be maintained and the data of other cells will be destroyed! Safety Merge of AddinTools Assist to merge the selected cells and maintain all data. Smart Unmerge to unmerge the selected cells and the data in every cell will be split into several cells! Cell Lens Of Data Type render and change the background color of cells according to their data type: Text to yellow, Number to blue, Date&Time to green ... Just cancel this Cell Len...

Calculations within summary cells, reflecting the visble cells only after filtering
Hi Wonder if someone can tell me whether it is possible to have calculations reflecting only the data of visible cells following a filtering exercise. For example, the columns below show data corresponsing to specific individuals (the name of the individual, initial, first date of absence, last date of absence, no of days absent and certification type). Below this data range is a cell with today's date and a cell with the date 12 months previous to this date. There is also a cell which reflects the total no of absence periods within this data range (20) - see calculation below. Would l...

query filter not like
I have a field that contains 6 digits, then an astrik, then 3 digits, then an astrik , then possible 2 digits, then an astrik and 1 digit. I need to filter for all but the records that contain the last astrick and 1 digit. I have tried "Not Like "######" & "*" & "###" & "*" & "#" & "*" & "#" - but no luck - can anyone help ? How about another tack. Seems that you want fields with less than 14 characters. Put something like this in the Field of the query. TheLenght: Len([TheFieldName) I...

formatting cells
Hi, I am trying to format cells that contain dollars in millions and make it decimal. i.e $1,500,000 would show $1.5 and the head of the table would say "Dollar Value in Millions" any ideas? Thanks Custom Format $0.0,, That's two commas after second 0 Gord Dibben MS Excel MVP On Tue, 16 Mar 2010 13:14:06 -0700 (PDT), pat67 <pbuscio@comcast.net> wrote: >Hi, I am trying to format cells that contain dollars in millions and >make it decimal. i.e $1,500,000 would show $1.5 and the head of the >table would say "Dollar Value in Millions" ...

How do I do a vertical merge of cells in a column?
Select your range in the column format|cells|alignment tab check Merge Cells PAM wrote: -- Dave Peterson ...

filter table so to get unmatched data
Hi there, I have very simple question but i could not figure out how to get the table. Here are two tables: Table A A B C D Table B A D I need a table which has all data from Table A but Table B. (unmatched data between Table A and B) Result Table B C Thanks for your time Musa Biralo Create a query in design view with both tables in the space above the grid area. Click on the field to compare in Table A and drag to Table B. Click on the resulting connecting line. Double click the line and select the option that reads show all records from Table A and on...

IF, COUNTIF
Can someone explain why these two expressions differ referring to the value 5? IF(A2:A10<5 etc. ) but COUNTIF(A2:A10,"<5") requires a comma and quotes Wouldn't it make sense for them to be the same? Just wondering. For us it would seem to make sense, but for the computer not so much :-) My understanding is that in the IF statement the <5 is a Comparison Operation which is a part of the 1st Function Argument whereas in the COUNTIF it is a Criteria Reference supplied as the 2nd independent Function Argument. If it stood alone as simply <5 it would ...

How To Freeze A Cell Once I finish With It?
I need a range to contain a function, freeze the range so it no longer changes, and then have the equation go to the same columns on the next set of rows. i'm using excel 2000. If you have a formula in that cell, then you can convert that formula to its value (edit|copy, edit|paste special|values). You may even want to lock the cell (format|cells|protection tab) and then protect the worksheet. Tools|protection|protect sheet Be aware that there are lots of things that won't work on protected worksheets. dan wrote: > > I need a range to contain a function,...

Cell format decimals
When you format columns to 2 decimal places, it looks correct, but the cell value is still 2.543678093 for example. I remember there is a way to change this to just 2.54. I just can't remember how to do it. I'm not talking about cell format, there is another way to do this right? -- Ramthebuffs ------------------------------------------------------------------------ Ramthebuffs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16429 View this thread: http://www.excelforum.com/showthread.php?threadid=480870 =round(A1,2) Where A1 holds the decimal v...

Closing Excel #4
The upper right X button on Excel is greyed out and I have to exit Excel from the File drop down menu, how do I enable the X (exit) button? -- RVarner RVarner, Step1 On the Tools Menu bar in Excel (Version up-to 2003) select Add-ins and ensure all are unchecked. Version 2007, File(round button)-Excel Options-Add-ins, Managed Add-ins, click the GO button. Again ensure all Add-ins are unchecked. Step2 Excel uses templates that may have become corrupted. Book.xlt is used for a new workbook Sheet.xlt, is used for new worksheets that gets added to existing workbooks. Both of these ...

Applying a filter to a report
Hi, I want the users of my database to be able to type a value in and get the report for that value. All values and information are saved in a table, and I've created a query with that information. For example, when I click on a report I'll call "Gum", I want Access to prompt me with a box so that I can put in an identifier number for a certain person. When I key in 39205, then this report will be filtered off of a query that I've made for all numbers but that report will give me only the information for this particular number. Then I can see when, where, how,...

Countif ...
I have a column with phone numbers. I want to count how many phone numbers have the area code "214" and "972". Thanks, the first formula worked. "Jason Morin" wrote: > The formula depends on how your phone numbers are formatted and whether they > are text or actual 10 digit numbers. For example, if they are text and you > simply need the first 3 numbers in the cell, try: > > =SUMPRODUCT(--(LEFT(A1:A10,3)={"214","972"})) > > If the area codes are enclosed in parentheses, you could use: > > =SUM(COUNTIF(A1:A10...

Freezing cells...
Hi...I have a set of stock figures which decrease whenever a sale is made and the data is entered in the right coloumn. I need to know how to make sure the clients who Im sending this spreadsheet out to do not input data in the stock column and messing up the spreadsheet. i.e. Stock Sales 4 0 3 1 3 1 2 2 So I need to make sure the stock coloumn is protected so the client cannot enter data into it. Hope this makes sense...Thanks in advance.. Hi - select the cells for which you want to allow entries - goto 'Format - Cells - Protection' an...

Exchange 2003 spam filtering capabilities
I'm going to be upgrading/replacing my Exchange 5.5 machine (running NT 4.5) with Exchange 2003. We do not currently have any anti-spam capabilities. Does Exchange 2003 have any built-in anti-spam abilities? What's involved in administering it? We are also going to be upgrading our Outlook Clients to Outlook 2003. Does Outlook 2003 have any built-in anti-spam abilities. What's involved in setting it up and administering it? Basically I need to know if I need to go back to my boss and ask for more money for a third party tool (like Inboxer), or, I can setup Exchange/Outlook 2003 ...