how can i use countif function inside a filtered range

How can I use countif function inside a filtered range
0
jayin (1)
2/24/2009 6:57:01 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
455 Views

Similar Articles

[PageSpeed] 45

Hi,

Not much detail here, so this is the general idea:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C13:C18,ROW(13:18)-13,0,1)),--(C13:C18="Red"))

-- 
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"jayin" wrote:

> How can I use countif function inside a filtered range
0
2/24/2009 7:05:01 AM
 Shane Devenshire wrote on 02/24/2009 02:05 ET :
> Hi,
> 
> Not much detail here, so this is the general idea:
> 
>
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C13:C18,ROW(13:18)-13,0,1)),--(C13:C18="Red"))
> 
> If this helps, please click the Yes button
> 
> Cheers,
> Shane Devenshire
> 
> 
> "jayin" wrote:
> 
>> How can I use countif function inside a filtered range
>> 
>> 
> 
 Hi
 can you advise how I would extend the command to include a further condition
 such as
 --(a13:a18=n2))
 
 I am trying to get the summary boxes to count visible rows only and where the
 project is closed and its budget is over the value in a cell i.e n2
 
 I have tried just adding it to the end but clearly that would be too easy !!
 
 Thanks
 Paul.
0
pauladkin (1)
6/18/2010 10:54:06 AM
Hi

Try
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C13:C18,ROW(13:18)-13,0,1)),
--(C13:C18="Red"),--(A13:A18=N2))


-- 

Regards
Roger Govier

"pauladkin" <pauladkin@domain-xyz.in> wrote in message 
news:QuWdnezoYI_TzYbRRVn_vwA@giganews.com...
> Shane Devenshire wrote on 02/24/2009 02:05 ET :
>> Hi,
>>
>> Not much detail here, so this is the general idea:
>>
>>
> =SUMPRODUCT(SUBTOTAL(3,OFFSET(C13:C18,ROW(13:18)-13,0,1)),--(C13:C18=&quot;Red&quot;))
>>
>> If this helps, please click the Yes button
>>
>> Cheers,
>> Shane Devenshire
>>
>>
>> &quot;jayin&quot; wrote:
>>
>>> How can I use countif function inside a filtered range
>>>
>>>
>>
> Hi
> can you advise how I would extend the command to include a further 
> condition
> such as
> --(a13:a18=n2))
>
> I am trying to get the summary boxes to count visible rows only and where 
> the
> project is closed and its budget is over the value in a cell i.e n2
>
> I have tried just adding it to the end but clearly that would be too easy 
> !!
>
> Thanks
> Paul.
>
> __________ Information from ESET Smart Security, version of virus 
> signature database 5206 (20100618) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
> 

__________ Information from ESET Smart Security, version of virus signature database 5206 (20100618) __________

The message was checked by ESET Smart Security.

http://www.eset.com



0
roger7155 (24)
6/18/2010 1:33:48 PM
Reply:

Similar Artilces:

Looking for an excel function which can mirror opposite the data
Dear sir, There are 2 data sets is assumed running in A1 down, viz.: From A1 down to A10, are showing a set of number: from 1 to 10 In the cell from B1 to B10, are showing from 10 to 1, which is decreasing from 10 to 1. My question: I want to know is there any excel formula which I can place in B1 and pick A10's number; B2 pick A9's number and etc. I know there is an easy way to do it, just type "=A10" in the cell of B1, but it is quite difficult to do it when I have more than 200 number, e.g. A1 to A200. Thanks for your advice, Wilchong -- Message posted via OfficeKB...

can't insert, delete or move pictures
can't insert, delete or move pictures Sheet protected? parker wrote: > > can't insert, delete or move pictures -- Dave Peterson ...

Need help to read Pie Chart Series Range
Excel 2003, I have an existing Pie Chart and want to extract the Ranges (cells) used. My code below returns with a "Type mismatch" error. Sub GetPieChartSeries() Dim mySeries As Series ActiveSheet.ChartObjects(1).Activate Set mySeries = ActiveChart.SeriesCollection(1) Debug.Print mySeries.XValues (errors here) Debug.Print mySeries.Values End Sub Thanks, - Pat ...

Hide sheets using vba
Hi all I have a sheet initally hidden(Contact) and the rest visiable, what I am trying to do is when today() = 1-11-06 it automatically unhides the Contact sheet and hides all the rest. I am trying to do it with having a button assigned to a macro - Having no luck... Any help welcome. Thanks Paul tried... Sheets("Menu").Range ("A1") >= "01/11/2006" GoTo hide Sub hide() Run Sheets(Array("Health Dec", "Menu", "UK", "EU", "XU", "WW", "AT EU", "AT WW", "Summary") ...

Determine framework version used by an application or a .dll
Hi, Is there a way to determine if an application (currently running in windows) is using the 3.0, 3.5 net framework?? I already tried several methods and lost many hours on this but I always get the CLR version and not the "real" framework version How do I get the used framework version (or the target framework) of an application? Thanks, On 5/2/2010 5:35 AM, SQACSharp wrote: > Hi, > > Is there a way to determine if an application (currently running > in windows) is using the 3.0, 3.5 net framework?? > > I already tried several metho...

Create employee attendance db using personal.xls?
I have limited, beginner VBA knowledge and have been researching online for ways to create an excel 'database' for employee attendance (worked time, holidays accumulated/used, overtime accumulated/used, other types of leave accumulated/used, etc). My research has led me to the idea of using Personal.xls by creating a Summary workbook to show all data from several sheets (1 sheet per employee). I was going to continue reading up on it but I didn't bookmark the page! (doh!). Is Personal.xls only for storing macros? Or can I put the employee data sheets in Persona...

Can I replace word by word in italics?
Is there a search/replace feature in Publisher that allows you to replace a word by the same word in Italics? -- Marv Not in Publisher, but you can in Word using wildcards. Might try copy/paste into Word, do the editing, copy/paste back into Publisher Help here http://www.gmayor.com/replace_using_wildcards.htm -- Mary Sauer http://msauer.mvps.org/ "WISEMANOFNARNIA" <COHENMARVIN@LYCOS.COM> wrote in message news:1f5149a6-f337-4db7-9aed-78132e9c9ae8@j12g2000vbl.googlegroups.com... > Is there a search/replace feature in Publisher that allows you to > replace a word by ...

How do I get a filter to "UPDATE" the rows selected?
Hey there! I am using Excel 2002 and I have a speadsheet that has prices in it. If I have the filter set to (the filter criteria is much more complex than this though) prices for items that cost >$5.00. Of I change an item to 4.59, it should not be seen. How can I get it to re-update the rows shown, with out releaseing the filter and resetting it? Is there something like refresh.filter or anything like that? Thanks Phil AFAIK you must release and reset but a macro with a worksheet_change event could trigger this for you so that it would happen automatically. -- Don Guillett SalesAid...

User defined functions aware of what cell they are placed in?
Hi, I would like to make a user defined function which needs to know in what cell and what worksheet it is placed in. I will be using this UDF in multiple cells on multiple worksheets. I originally just passed the cell row and column as parameters to the UDF however this ended up updating all worksheets and not just the one the UDF was on. Is there any way to do this? Option Explicit function myfunct(something as somethingelse) as something msgbox application.caller.address & vblf _ & application.caller.parent.name & vblf _ & application.caller.pare...

copy date in a cell if within a date range
Column M is a listing of percentages Column A is various dates, anywhere from Jan 1, 1998 to the present. I need to copy the contents of let's say M3 into cell T3 is the date in cell A3 is any date in the year 2010. If the date is in another year, leave cell T3 blank Thanks "carrerapaolo" wrote: > Column M is a listing of percentages > > Column A is various dates, anywhere from Jan 1, 1998 to the present. > > I need to copy the contents of let's say M3 into cell T3 is the date in cell > A3 is any date in the year 2010. If the ...

can not delete emails
i went out of town and returned with 3000+ emails. some i can delete, some i can not. i have tried to repair and fix, i have tried to create a new profile and import/export everything. any ideas? thanks in advance. yes, the cannot move item(s). The initial error i get when trying to delete is: "the message interface has returned an unknown error. if this persists, restart outlook". The only thing i can think of is a damaged profile??? >-----Original Message----- >Do you get an error when trying to move the item to another folder (e.g. >move the item to the deleted ...

Can I retrieve deleted email from the enlarged database
Version: v.X Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Email Client: pop My v.X database is 950Mb with very little email. I know this is because of the deleted emails of the past and that rebuilding the database would produce a new database the right size. I would like to know if I can get to the deleted emails. Can I get them back. I have run in to an issue that I need them. <FONT FACE="Calibri, Verdana, Helvetica, Arial"><SPAN STYLE='font-size:12pt'>On 5/11/10 11:41 PM, in article <a href="59bb859f.-1@webcrossing.JaKIaxP2ac0&q...

How can I line up 2 columns with similar information?
I have 2 columns with names in each and I'd like to line them up next to each other. I think we'll need more information, Kathy. I don't understand what you're trying to do. Can you give exactly what's up? ************ Anne Troy www.OfficeArticles.com "Kathy" <Kathy@discussions.microsoft.com> wrote in message news:A5C0BC96-7E27-4C55-89A3-03513FC0D409@microsoft.com... >I have 2 columns with names in each and I'd like to line them up next to >each > other. Column A has employee number, Column B has the same employee numbers but has addi...

Switching companies using SQL Passthrough
I have an application that uses SQL_Passthrough. As part of the code you must execute a statement that uses the appropriate database. The code looks like this: set SQL_Statements to "use MYDB"; status = SQL_Execute(SQL_connection, field SQL_Statements); This works fine, but my application can be used for any number of databases. At first, I modified it to use the Dex.ini file, which works. Here is the modification: dbname = Defaults_Read("SQLDB"); dbopencommand = "use " + dbname; set SQL_Statements to dbopencommand; status = SQL_Execute(SQL_connection,...

Using Word 2003 in Vista: Opening dictionary shuts down Word
This is a problem in Vista; it did not occur when I used Word 2003 in XP. Whenever I try to open the dictionary in Word 2003, either by clicking its icon, or hitting Alt+click over a word, Word shuts down. Vista Business, Service Pack 2 Thinkpad T400, Intel Core 2 Duo CPU, 3GB RAM ...

=IF(NOT(ISNA(VLOOKUP Function
I am using Excel 2003 I am trying to produce a summary shett from five differnet worksheets. I am using the following function =IF(NOT(ISNA(VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE))),VLOOKUP(E7,Tests!D1:Tests!S147,18,FALSE),""). It works fine until I reach 18 and above and it returns #REF!. Does this formula not work after 17 or is there another formula that I should be using. Colin Hi, The VLOOKUP() table array cannot span across sheets. Do let us know what you are trying to do? -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com ...

Using namespaces? I've some messy nested contexts that I want to clean up...
Hi, I've got the following code structure Class A { ... private: Class B { public: enum C { ENUM X } C MyVar; C MyFunc() } } So for function definitions in B I have to write A::B::C A::B::MyFunc() and for objects of B in A if(pb->MyVar==B::ENUM_X) It's all just a bit messy. Isn't it. Someone please help. Regards. ...

Old emails not displayed (Multiple PC's using same inbox)
I have 2 machines running XP and Outlook 2000. Both mahcines are setup to using the same Exchange Server email account. When i open outlook in one machine all mail are visable is the inbox, and new mail is received correctly. But if i opne Outlook on the second machine my inbox is empty. Any new mails will appear quickly then disappear. I cannot get the second machine to display old emails. If I leave the first machine off, all new mail will stay in the second machines inbox, but if I open outlook on the second machine all mail disappears and is shown in the first machines inbox. A...

ProbleM: when I restore a mailbox using Exmerge with a pst file, nothing is transferred.
Hi, I am practising Exmerge for a big remote site migration in a couple of weeks. One thing I dont understand is that I can backup one test mailbox fine using Exmerge (I know this works, as I have opened the mailbox pst file within outlook and everything is there), but when I perform the restore using the pst file, nothing happens. There is no error messages, and Emerge goes through the motions (though it finishes supsiciously quick), but when I open the mailbox, no emails have been restored. Although it is great that Exmerge is working for the backup part of the stage, I am disappointed it i...

Can MsPubliser do INCLUDETEXT or MasterDocument/SubDocument equiv?
I'm preparing a series of bifold + duplexed booklets. There are 8 page, 16 page, 24 page, and 40 page versions with much duplicated text. I've used Word's Master-Document with sub-documents (and also the INCLUDETEXT fieldcode) so that changes / typos only have to be fixed in one file. I'd like to use the same MasterDocument approach with Microsoft Publisher 2003. Is this capability available with Publisher? This would be for duplexed quad-fold versions that end up being 4.25"x5.5". Does Publisher 2003 have the feature of a master document being able to include separ...

Creating the filter for the recipeint policy
Hi, Can you let me know what is the best of way of creating the LDAP filter for the recipient policy if I want the policy to assign the email address based on the admin group.This filter should assign the email address to the mailbox and DL's -- Vinod Kumar Recipient Policies are based on recipients' attributes - admin group is not one of them. Perhaps look at other common attributes, or add an additional UPN suffix and assign a common UPN suffix to these recipients, than use Recipient Policies to filter based on UPN. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog l...

Formula to process 3 cells using IF statements
I have 3 columns of experimental data (C:E). Row 30 contains the sums (C30:E30). I need a formula that will examine the three sums and return the column number that has the lowest sum. If more than one column is lowest, select one randomly. Example: C30 D30 E30 Result 10 11 12 1 (C) 22 20 21 2 (D) 32 31 30 3 (E) 40 41 40 Randomly select 1 or 3 51 50 50 Randomly select 2 or 3 60 60 60 Randonly select 1, 2, or 3 Can this be done with IF statements or do I need to write a macro? Well, this is a bit cumbersome, but it se...

Publisher calendar, how can I show the previous/next month?
I need to show the past and previous months in blank days at the top of a per month calendar, but can't see how to do that. Thanks. Create a yearly calendar in a new publication. Ungroup, copy the separate months, paste to your main calendar. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jennie" <Jennie@discussions.microsoft.com> wrote in message news:AF4CF698-ABB8-4F01-A667-CC6AA87A9732@microsoft.com... >I need to show the past and previous months in blank days at the top of a per > month calendar, but...

Help on filter
HI, I have a list to filter. I have to keep only integers. I tried everything in advanced filters... does not work. Can you help me please? Thanks Sylvai -- Message posted from http://www.ExcelForum.com Hi one way: use a helper column. Lets say your numbers are in column A then enter the following formula in an adjacent cell for row 2 =IF(MOD(A1,1)=0,"X","") copy this down for all rows. After this filter with this helper column -- Regards Frank Kabel Frankfurt, Germany > HI, > I have a list to filter. I have to keep only integers. > I tried everything in ...

Pointing to correct macro path using excel custom toolbar
I have created an excel 2000 template (.xlt) containing a number o macros. When I open copies of this template on various pcs, the macro function correctly, except I cannot successfully run the macros usin the custom toolbar I created, because (I think) within the toolbar th paths to the macros are pointed to the original location on my pc. An advice on how I can resolve this would be gratefully received -- Message posted from http://www.ExcelForum.com Have you thought about building the toolbar when the file opens? Or maybe separating the worksheet portion of the template from the code pa...