How to use count with multiple conditions

I have a table in Excel:
The first row is time in years.
The second row is method name,say,"A","B","C".

I want to count the number when the time is less than 5 years AND "A"
method is adopted.

I tried this:
count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.

Any suggestion on how to revise the formula?

Thanks!

In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as
countif(C2:Z2,"<5")

Ming

0
zhongming (15)
8/4/2005 5:15:32 PM
excel 39879 articles. 2 followers. Follow

8 Replies
487 Views

Similar Articles

[PageSpeed] 38

=SUMPRODUCT(--(C2:Z2<5),--(C3:Z3="A"))

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Ming" <zhongming@gmail.com> wrote in message
news:1123175732.501968.278510@g14g2000cwa.googlegroups.com...
> I have a table in Excel:
> The first row is time in years.
> The second row is method name,say,"A","B","C".
>
> I want to count the number when the time is less than 5 years AND "A"
> method is adopted.
>
> I tried this:
> count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.
>
> Any suggestion on how to revise the formula?
>
> Thanks!
>
> In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as
> countif(C2:Z2,"<5")
>
> Ming
>


0
bob.phillips1 (6510)
8/4/2005 5:42:50 PM
Or enter the following as an array:
count(if(C2:Z2<5,if(C3:Z3=3D"A"=AD,C2:Z2))

To enter it as an array hit ctrl-shift-enter, rather than just the
<enter> key.

Jim Shoenfelt

0
jimhome (13)
8/4/2005 6:45:24 PM
Hi,Bob

what's the meaning of the operator "--" in your formula? To transfer
logic value into 1/0?

Thanks!
Ming

0
zhongming (15)
8/4/2005 7:00:44 PM
It works!
Thanks, Jim

0
zhongming (15)
8/4/2005 7:01:10 PM
Exactly that.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Ming" <zhongming@gmail.com> wrote in message
news:1123182044.577517.242550@g49g2000cwa.googlegroups.com...
> Hi,Bob
>
> what's the meaning of the operator "--" in your formula? To transfer
> logic value into 1/0?
>
> Thanks!
> Ming
>


0
bob.phillips1 (6510)
8/4/2005 7:18:17 PM
Hi! Guys, I also wonder how to use COUNT when I have to count the
absolute values. Say I have M1:M100 and I want to count that how mang
the values within this range whose absolute values are smaller than 1.
I tried COUNTIF(ABS(M1:M100), "<1) or COUNT(IF(ABS(M1:M100),<1,
M1:M100). Both didn't work.  Any suggestions to make it right? Thank
you in advance!

0
8/5/2005 4:10:03 PM
=SUMPRODUCT(--(M1:M100<>""),--(ABS(M1:M100)<1))

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"hong" <wangyuhong@gmail.com> wrote in message
news:1123258203.301370.135180@o13g2000cwo.googlegroups.com...
> Hi! Guys, I also wonder how to use COUNT when I have to count the
> absolute values. Say I have M1:M100 and I want to count that how mang
> the values within this range whose absolute values are smaller than 1.
> I tried COUNTIF(ABS(M1:M100), "<1) or COUNT(IF(ABS(M1:M100),<1,
> M1:M100). Both didn't work.  Any suggestions to make it right? Thank
> you in advance!
>


0
bob.phillips1 (6510)
8/5/2005 5:37:46 PM
Thanks! Bob, it works perfect.

0
8/5/2005 7:15:03 PM
Reply:

Similar Artilces:

Switching between multiple open windows
I ran across an option to change how to switch between multiple open windows on my desktop. It was an option to choose between hovering over a window or clicking on it. Thinking that only having to hover over a window to switch to it would save time, I selecting the option to hover... big mistake as it just became a big pain in the a_ _ for me. Now I would like to change back to one-clicking to change between viewed windows but can't remember or find where the option is located. Any help will be appreciated. -- rushl On 2/03/10, rushl posted: > I ran across an opti...

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

Excel-Multiple Cells Being Hi-lited
Sometimes when I'm setting up a worksheet and I left-click in a cell, multiple cells in the same column are hi-lited. After it happens the first time, it continues as I move through the worksheet, reducing my ability to get work done considerably. After some trial and error, it seems to occur when I've been adding and/or deleting columns and/or rows, after a header has been installed. I can move throughout the worksheet using the arrow keys, but it is a time consuming and cumbersome technique. I think the version I'm using is Office Professional 2007 (file extensi...

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

CtreeCtrl multiple selection
Hi, Is there any simple way (samples) to set a CTreeCtrl has multiple selection feature? Thanks, Chi Try these: http://www.techsoft.no/bendik/ http://www.codeguru.com/Cpp/controls/treeview/misc-advanced/article.php/c723 http://www.codeguru.com/Cpp/controls/treeview/misc-advanced/article.php/c629 -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Chi" <anonymous@discussions.microsoft.com> wrote in message news:21bd01c4aa63$2e355d70$a501280a@phx.gbl... > Hi, > > Is there any simple way (samples) to set a CTreeCtrl has > multiple selection feature? Thanks, > &...

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

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

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

multiple users...again
My wife and I have road runner cable. We each have our own email account. We want to use Outlook 2003 to read our respected emails. I've tried to configure it so that we each have our own password for outlook for privacy of email. As it stands now, when I click on the Outlook shortcut on the desktop it asks for my password. I give it and it opens up my email folder. I tried to create a folder for my wife's email. I put a password on her folder. Now when I open outlook from the shortcut on the desktop, it want's my password. The program opens and then to get to her f...

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

center text when conditional format applied
Textbox control on Access 2007 form lost its centered alignment when conditional formatting is applied. This did not happen in previous versions of Access. I tried using format painter to apply conditional formats from a control created in an earlier version, but this failed. The control created in the earlier version then lost it's centering after saving in Access 2007. -- Roy Handy Interesting. I tried to replicate this behaviour but failed, it all worked as it should. Try revisiting your form after applying the conditional formatting and resetting the text box to center text. ...

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

Entire Visio page moves whenever I use the directional arrow keys instead of the object I've clicked on
Gurus, Running Visio 2003. For some reason lately, whenever I click on an object and try to move it using the right, left, or up or down arrow keys, the whole page moves instead of the object I've clicked on. This is really annoying! It didn't used to be this way. I'm not sure what I changed. I simply want the object I've highlighted to move whenever I use the arrow keys not the whole Visio page itself! -- Spin On Fri, 24 Oct 2008 17:29:14 -0400, "Spin" <Spin@invalid.com> wrote: >Gurus, > >Running Visio 2003. For some reason lately, whe...

Microsoft publisher, how do I set it to show multiple pages?
I'm trying to show more than one page in a single viewing pane. As in for the use of a banner. You'd think that'd be a simple and accesable function. I don't see it anywhere... Print preview has this function. Not sure if this is what you are asking... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "BrickShort" <BrickShort@discussions.microsoft.com> wrote in message news:B42E0DAE-9DB6-4587-A157-3C2F5225FA02@microsoft.com... > I'...

how do i enter data for a # of years using a formula?
i am working on excel and the book asks that i enter data s=using formulas for specifically the last three years of what i am referencing to. and i have to know how to us the copy command button. can anyone help ...

multiple vendors or buying groups
Hello: I have a PO that may eventually be charged, by percentage to more that one vendor. So, they are buying a product that is say 25% to one vendor and 75% to the other. How can we do this? Any suggestions? Additional software? VB? Please let me know. -- Kate Hulit Asyma Systems Inc. ...

Supporting Multiple screen resolution
Hi How i can support multiple screen resolution with MFC? Thanks, Ritu What does this mean? Are you asking about dual monitor support? -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Ritu chawla" <rituc@momentum-tech.com> wrote in message news:u$xGo0GbDHA.2404@TK2MSFTNGP10.phx.gbl... > Hi > How i can support multiple screen resolution with MFC? > Thanks, > Ritu > > Hi i am working on an application whose GUI becomes completely visible in 1024 x 800 size only. i need to give support or 800 x 600 also. how can i do this. rgds, ritu "Ajay Kalra"...

.NET 3.5
Hello, Does anyone of you know how I can force vb.net to remove the handle of a local DLL which I used beforehand with Reflection. Here is a fragment of the code. I have a base class and a derived class. This app only knows the base class and creates an Instance of the derived class by using Reflection which works fine. The only problem I have is that the local DLL file is blocked (I can't delete it) as long as I don't close the Application Dim GetCustomModule as BaseClass Dim dll As Assembly = Assembly.LoadFrom("c:\tools\DerivedCass.dl...

Fill Down based on count in another sheet
I am using the following in Excel to copy a formula: ThisWorkbook.Worksheets("Results 4").Range("3:2000").FillDown I need to only copy the formula for a certain number of rows up to 2000. The number of rows I need is based on the number of rows I have in worksheet "Results Data" in column A. So, if "Results Data" has data in column A2:A16, I will need to copy down the formula for all columns in worksheet "Results 4" to column 17. Sub FillFown() Dim lStop As Long With Worksheets("Results Data") lStop = .Cells(...

Multiple Forest Solutions
Hi All, I have a situation where a client is dealing with 2 seperate Win2K3 forests. What type of solutions are available when there is a fully transitive trust relationship between forests and two seperate Exchange orginizations? Some of the things they want to do is share calanders between the forests, share address books, ect. Any help would be welcome! TIA Paul Identity Integration Feature Pack (IIFP) to share the address books. Inter-Organization Free/Busy Replication Tool to share free/busy. If you google them, you should be able to find scenario walkthroughs, in addition t...

Ex 2003 IS backup, using NTBackup 5.0
On Win2K SP4, I've upgraded from Ex 2000 ==> Ex 2003. I use NTBackup to back up the IS. The NTBackup is version 5.0. Backup proceeds normally; on differentials I can see NTBackup counting log files, etc. However, I tested a restore, and when I catalogued the *.bkf file, NTBackup says "No files were found". So I have an error-free backup, from which (apparently) I cannot restore. Is the Ex 2000 ==> Ex 2003 upgrade supposed to upgrade NTBackup as well, to a version that can handle restoring Ex 2003 IS? Does anyone have an insight into why NTBackup doesn't want to ...

Option trades not using "cash" account
I am using the trial version of Money 2006 Premium and am seeing a problem which also occured in Money 2003. When opening or closing an option position in an investment account, the "Transfer to:" field is left blank by default even though a "cash portion" is defined for that account. This is a minor annoyance in that, for each of these transactions, I need to select the appropriate cash account. In some cases, if the "Transfer to:" field is left blank, Money will ask for the cash account name but this does not always happen and the security is bought or...

Off-Topic: Newsgroup Readers/Viewers
I know this is off-topic, but I couldn't find a newsgroup that was appropriate for this post (like a lounge for MVP's or something)...so I apologize in advance. I currently use Outlook Express (or Outlook Newsreader : "C:\Program Files\Outlook Express\msimn.exe" /outnews) for viewing newsgroups. While its a nice program, it does have a few shortcomings, not the least of which is its difficulty in truly tracking and reporting responses to posts made by the user. I figured since a lot of MVP's and others on these newsgroups tend to respond to a lot of newsgroup posts, an...

Inserting comment depending on the Conditional formatting
Hi Is it possible to insert comment depending on the conditiona formatting.I mean if the color of the cell is blue the comment shown i " A " & if the color of the cell is green the comment shown is " B " similarly if the color of the cell is black the comment shown is " C . ( The color names which i had writted here r examples i dont know th real names of the colors which i have given in my spreadsheet) -- Little Maste ----------------------------------------------------------------------- Little Master's Profile: http://www.excelforum.com/member.php?acti...

Using "Avg" function on calculated field
In a report I have a calculated field as "=Nz([Total],0)/Hrs" which works fine. In the report footer I tried to get the average of the values in this column so I used "=Avg(Nz([Total],0)/Hrs) but when I run it it asks me for the value of "Hrs". Neither "hrs" nor "total" are calculated fields and no error is generated either. The output for the average is just blank. I tried =Avg(Nz([Total],0)/(Nz(Hrs,))) which did not work either. What am I missing? There's a couple of potential issues here. It sounds like Hrs is a field in the table/quer...