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

Similar Articles

[PageSpeed] 6

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

Use Form to prompt for report criteria
I have a form that I am using to prompt for report criteria. When I run the query outside of the form, it works fine - prompting me for both criteria. However when I run from the form, I get #Error#. Can you see what I am doing wrong? Thanks in advance. I have two combo boxes that I have put in my underlying query. In the fields of the query are: [Forms]![frmSelection Criteria Form]![OfficeNumber] [Forms]![frmSelection Criteria Form]![Manager] *** On the OnClick event is the following: Private Sub Command6_Click() On Error GoTo Err_command6_Click Dim stDocName As String st...

Physical Inventory Count Service Provider
Would someone be able to recommend a service provider that would be able to come in an apparel retail store and do a physical inventory count? we've tried newestech handhelds, but we just dont have the right staff to do it. TIA -- Eddie B No suggestions? We are in Southern California. -- Eddie B "Eddie B" wrote: > Would someone be able to recommend a service provider that would be able to > come in an apparel retail store and do a physical inventory count? we've > tried newestech handhelds, but we just dont have the right staff to do it. > > TIA ...

Creating a new field based on conditions
I have a database that tracks insurance information for our various vendors. Each insurance type has 2 fields - a requirement field (yes/no), and an effective field (some show an expiration date, some are yes/no). I have created a query that will return only the records for which insurance is required but is expired/missing. My problem is that I want to create a new field that is calculated based on the values in the other two fields in order to make the resulting report more user-friendly. For example, if GLRequired is True and GLExpiration is <Now(), I want the new field to say...

Controlling multiple TCP connections from Outlook 2003 clients
I've noticed that Outlook 2003 clients can have multiple TCP connections to the mailbox store on Exchange. As the following web link claims: http://www.windowsitpro.com/Article/ArticleID/46319/46319.html "This behavior is by design. Outlook can open multiple connections to parallelize its data communications with the Exchange server. The actual number of connections will vary according to the version of Outlook that you're using and the mode in which you're using it. For Microsoft Office Outlook 2003 in Cached Exchange Mode, you'll see four connections for the mailbox, ...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Select all of a certain column across multiple worksheets
Is there an easy way to simultaneously select (or do a find-and-replace) on column H on every worksheet of an entire multi-sheet workbook, without having to select that column on each individual worksheet by hand? Charles Belov SFMTA Webmaster http://www.sfmta.com/webmaster Right-click on first sheet tab and "Select all sheets" In activesheet select the column............will be selected on all sheets. Gord Dibben MS Excel MVP On Mon, 24 Aug 2009 16:43:02 -0700, "Charles Belov" <invalid@invalid.invalid> wrote: >Is there an easy way to simultaneously se...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

selected row count of list box
A2k Is there a way to get the selected row count of a list box dynamically as the user selects rows? Delphi has an event called "OnSelectionChanged" but Access is much more limited. I don't want the user to have to exit the list box or click a button or anything manual in order to see the # of rows he's selected. How can this be done? I know about "lstCusts.ItemsSelected.Count" but not sure what event to use it in to accomplish what I need. Thanks, Keith Never mind. I did this and it handles both mouse and keyboard selections: Private Sub lstCusts_AfterUpdat...

Monthly count
HI, I have a table with Product field and SaleDate field. I need to make a query that will give me a count of number of times a product was sold on a monthly basis. In other words, the number of records for each product in a month. Something like each productname as columnheading and name of the month as row heading, and the count in the tabulation. do i need to use crosstab query to do this? could someone guide me here? Thanks very much. Ramesh 1. Create a query using your table(s). 2. Change it to a Crosstab query (Crosstab on Query menu.) 3. Type this expression into the F...

Sumproduct with multiple date criteria
Having a tough time with this one. Sheet 1 Column A = Start Date, Column B = End Date, Column C = Quantity. Sheet 2 Row A = Start Date, Row B = End Date. I would like Row C to sum quantity from sheet 1 where ever the two date ranges intersect. The date ranges on sheet 2 represent the beginning and ending of a week (Mon-Sun). Sheet 1 Column A Column B Column C 01JAN2010 24JAN2010 1,000 Sheet 2 Row A 04JAN2010 11JAN2010 18JAN2010 25JAN2010 Row B 10JAN2010 17JAN2010 24JAN2010 31JAN2010 Row c 1,000 1,000 1,000 0 Do the Sheet 2 Star...

Using atl based win dll with CString functions from the mfc projec #3
I have atl based general windows dll with class which contains functions which uses CString as parameters or return values. This dll might be used from the atl or mfc project. Dll can be used from ATL project without problems but whenever I try to use this class from the MFC project I get the following linker errors: error LNK2019: unresolved external symbol "__declspec(dllimport) public: int __thiscall MyClass::AddMenu(long,class ATL::CStringT<wchar_t,class StrTraitMFC_DLL<wchar_t,class ATL::ChTraitsCRT<wchar_t> > > const &,long)" ... If I replace CStri...

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

conditional formatting in excel #3
how do you add a phrase to a field if the filed is blankl, also, can you have a notifiction sent to you when a date on a spreadsheet has expired? > how do you add a phrase to a field if the filed is blankl, What "phrase" do you mean? A Comment? A value? also, can you have > a notifiction sent to you when a date on a spreadsheet has expired? Maybe you can apply an open event (date to be tested being say in F1): Private Sub Workbook_Open() If Range("F1") < Date Then MsgBox "Date expired" End If End Sub Regards, Stefi ...

Not using zeros in graphing.
I have a running workbook that has tons of information. I have added a sum page in order to have all the data summed up in one simple place. I have formulas that read back into the workbook to link to a cell. Depending on what moth it is, that cell could be empty as it is a yearly wookbook. For example, if this is August, then there is information in the workbook up to August, but none after. With that said, the sum page has the #DIV/0! in the cell which essentially equals zero. I also have graphs that I have linked to this sum page. My problem is in order to keep the graphs up to...

Need Help with drop down list with conditions...
I need help. I'm having a very difficult time creating a sequence of list which change based on the item selected on the main list, lets call it the index: The Index list, the first list, would define vendors. In this case Hotel Companies (e.g Hilton, Marriott, Sheraton). The following list, the 2nd list, would define properties, but the properties would vary based on which hotel company is selected (e.g. If Hilton, then "The Plaza, the Waldorf Astoria. If Marriott, then Marriott Marquis, Marriott Convention Center etc etc...) on list #1. And Last but not least, the 3rd list would...

using vba so search multiple Sheets
i'm trying to search across multiple sheets based on data submitted via an input box. So essentially, you click the button, a box appears, you type what your looking for and if it finds it, it'll select it otherwise a error message appeats. i found the below code, but it only works if the cell with the value in it is active (i.e. i've clicked on it). Code: Dim datatoFind Dim sheetCount As Integer Dim counter As Integer Dim currentSheet As Integer On Error Resume Next currentSheet = ActiveSheet.Index datatoFind = InputBox("Please enter the value to search...

DPM and VMM on same server using side-by-side SQL installation
Windows 2008 Std R2 Server, 64bit VMM 2007 is currently installed (with the built-in default database) SQL 2005 Express Edition, SP3, 32bit. I was advised by Microsoft that both VMM and DPM could be installed on the same server (small DR environment) The VMs are hosted on a Windows 2008 Enterprise Server w/ VS2005R2 - because the hardware didn't support HyperV. VMM works great to manage the Windows 2008 VS2005R2 Host running Win2K3 server instances ... after lots of trial and error. Now, I need to install DPM 2007 onto the same server... DPM wants to install the 64bit vers...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...

Using "fixed" decimal place function data for charts
Is there a way to graph data which utilizes the "fixed" function for decimal places? When I try this it will not graph, as the data has become text or something - even though I've selected number in the cell formats. If you've fixed the number of digits using only number formats, the values remain numeric, and they should chart just fine. This means selecting the range, going to the Formatting menu and selecting Cells, and on the Number tab, selecting Number in the category list, and setting a number of digits. I'm not familiar with a "fixed function". I...

slow sync with Calendar using Kyocera 7135 via Outlook 2003
I'm stuck and need help. I have a Two Day Old Brand New Dell Dimension 4600. Does anyone out there know what to do when Outlook 2003 is giving a Log report that reads that Outlook Calendar - Slow Sync synchronization failed? This is a first time occurence for me. Outlook also reported that a device is trying to get access to my E- Mail addresses which may indicate a Virus may be present. I've run my McAfee Virus Protection and it didn't detrect anything. I'm at my wits end and don't know what else to do. Can anybody help? ...

invalid use of property message
I am trying to cre4ate a macro that checks the value of a number of sheets to determine if they are already visible and if so it will not close them - With Sheets("Pay Inflation - Biometrics") Sheets ("Statistics") Sheets ("Direct Cost Savings Breakdown") Sheets ("OT Reduction") Sheets ("Nurse OT Reduction") Sheets ("Premium Labor Utilization") Sheets ("Pay inflation - Timestamp") Sheets ("Calculation Error") Sheets ("Le...

can not Send/receive using Outlook 2003
Just bouhght new computer and am loading all of my software that worked onmy old one. When I try to send and recieve I get the message object not found. I have used test and this work ok. I have also reloaded all of my broadband and office software numerous times to no prevail. Software Windows xp Office 2003 ADSlL broadband with bigpond Gp Check to make sure the accounts are setup correctly if they are then run Detect and Repair, still an issue then create a new profile. May also want to check your anti-virus software program setting for outgoing mail and disable that option. Rega...

Email to multiple people but show only the recepient name
Is there a way to send to ten people at once but only have one name in the To: box at a time? So that the entire list of people is not sent. Use the BCC field. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Terry asked: | Is there a way to send to ten people at once but only | have one name in the To: box at a time? So that the | entire list of people is not sen...

How do I get Outlook Today to show appts from multiple calendars?
I want the opening page (Outlook Today) to show the next week's appointments from 2 calendars. I can only get it to show one calendar and it defeats the purpose of having the summary. Ideas? If you provide some basic information like: version of Outlook where the two Calendars are located (eg is one a Public Folder, a shared Calendar, a sub-folder of your mail Calendar) you'll find getting a useful answer here much faster and less laborious. :-) Regards Judy Gleeson MVP Outlook in Canberra, Australia "MORE CALENDAR" <MORE CALENDAR@discussions....

using rule creates multiple entries. How can I stop this?
I'm going to give you the details of this so maybe someone can just tell me how to correct or whether it can be corrected. I've tried creating different rules, rules with exceptions, multiple rules with multiple exceptions, all I can think. Here is the scenario. Although I'm using Lisa as an example, this problem applies to every staffer in my 9 person office. Lisa is my secretary. She sends me internal email, staff questions and stuff which goes to a a "Staff" subfolder of the Inbox. I have a rule for this. She also sends me mail she has scanned. I ha...