V-lookup and Countif

I'm trying to create a formula that will look up criteria in one column and 
count the result of another column.  Here is some data as an example:

Fruit        Have in Stock   
Orange     X
Pear         X
Apple
Orange     X
Orange
Apple       X

I'm trying to create a formula that looks up "Orange" in column A and then 
counts the number of "X" in column b to return a results.  So my result for 
Oranges should be 2 because out of the 3 listed only 2 have an "X" marked 
next to it.
0
Utf
12/23/2009 7:39:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1768 Views

Similar Articles

[PageSpeed] 17

Hi,

Try this

=SUMPRODUCT((A1:A10="Orange")*(B1:B10="X"))

In practice use cell references for the criteria

=SUMPRODUCT((A1:A10=C1B1:B10=D1))

Mike

"thm" wrote:

> I'm trying to create a formula that will look up criteria in one column and 
> count the result of another column.  Here is some data as an example:
> 
> Fruit        Have in Stock   
> Orange     X
> Pear         X
> Apple
> Orange     X
> Orange
> Apple       X
> 
> I'm trying to create a formula that looks up "Orange" in column A and then 
> counts the number of "X" in column b to return a results.  So my result for 
> Oranges should be 2 because out of the 3 listed only 2 have an "X" marked 
> next to it.
0
Utf
12/23/2009 7:59:01 PM
I meant

> =SUMPRODUCT((A1:A10=C1)*(B1:B10=D1))

"Mike H" wrote:

> Hi,
> 
> Try this
> 
> =SUMPRODUCT((A1:A10="Orange")*(B1:B10="X"))
> 
> In practice use cell references for the criteria
> 
> =SUMPRODUCT((A1:A10=C1B1:B10=D1))
> 
> Mike
> 
> "thm" wrote:
> 
> > I'm trying to create a formula that will look up criteria in one column and 
> > count the result of another column.  Here is some data as an example:
> > 
> > Fruit        Have in Stock   
> > Orange     X
> > Pear         X
> > Apple
> > Orange     X
> > Orange
> > Apple       X
> > 
> > I'm trying to create a formula that looks up "Orange" in column A and then 
> > counts the number of "X" in column b to return a results.  So my result for 
> > Oranges should be 2 because out of the 3 listed only 2 have an "X" marked 
> > next to it.
0
Utf
12/23/2009 8:43:01 PM
Reply:

Similar Artilces:

Chinese fonts on Office v. X
I am having problems reading my documents that I receive from Taiwan and China (both simplified and traditional characters). When I usually receive an email with Chinese. . . I copy/paste them to Sherlock's translator and viola!. . . I get English. . . But this is not the case with attachments from emails in the form of excel or word files. I usually first have an issue in trying to find out which font they used so that the Chinese characters come out. Once they come out, I can print it out. . .but I need them translated. . . Unfortunately, I cannot copy/paste the characters at all. . ....

Lead v/s contact v/s account v/s opportunity
Hi, I am trying to understand a few basic things about Microsoft CRM. I would appreciate it if someone can enlighten me on the following questions: 1. Is the following definition of "Lead" correct? A lead is a person who we come to know from somewhere. Technically, a lead must have a name and optionally the company the lead belongs to. According to the CRM UI, a lead may be converted to one or more of the following: a) an opportunity b) a contact c) an account. Or, a lead may just disqualify. 2. What is a contact? When would I convert a lead into a contact? 3. What is an accoun...

Multiple V-Lookup
I am trying to do a Vlookup function that will look at 2 cells on the same row in one tab and find that in the workbook on the other tab in the same row and then spit back a number from a different cell. It's like a regular vlookup but i'm trying to search for 2 values in 2 different cells at the same time. Is this possible? Maybe it's not even a vlookup i should be using. The problem with using an if(and()) statement is i can only search for the combination 1 row at a time, so if it doesn't line up, i won't get a match. I need to search the whole workbo...

Lookup and count in the same formula
Hello, I need a formula that looks up a value in row 1 on sheet2 and returns the count of a range in the colmun that matches the lookup in sheet1. For example: Sheet2 - Feed Sheet Smithfield RSM John Doe ASM Jenn Smith ASM2 Brian Smith So Smithfield has 1 RSM and 2 ASMs Sheet1 RSM ASM Smithfield 1 2 So I want a formula to return the count of how many RSM's and ASM's are in a specific location. I have this: =(HLOOKUP(A1,Sheet2!A1:A6,(COUNTA(Sheet2!A5:A6)),FALSE)) but it's just retur...

The Lookup Wizard, Do I Hate It?
ok. So, this week the Lookup Wizard inflicted me with my first experience of it. I'm talking about the Lookup Wizard that appears in the Table Design interface. Here's the good news, you can bring up a table, and your field has a drop down and second level reference automatically working. The bad news is it presents the second level lookup everywhere, for everything. But sometimes I just want to see the index actually recorded in the table. If I build a query, and I just want the actual index rather than its translation, how can I get that? Is there a way to undo the autom...

Countif for finding a Time input
Got a big long column D containing times formatted as i.e. 13:30. I am trying to do a countif function for finding the count of inputs between 6 am and 6:59 am, and 7am and 7:59 etc. Can't find the right combination of using logic => and < within the countif formula... appreciate any help here.. Hi, maybe this for 06:00 to 06:59:59 =SUMPRODUCT((D1:D8>=TIME(6,0,0))*(D1:D8<TIME(7,0,0))) or you can do this for 06:00 to 06:59:59 and drag down for subsequent hours =SUMPRODUCT((D1:D8>=TIME(ROW(A6),0,0))*(D1:D8<TIME(ROW(A7),0,0))) Mike When comp...

SBS2003 and Hyper-v
We want to reduce equipment in the lab. We have a well functioning SBS2008 Standard domain running with one sbs2008 (8 GB) server and a second win2008 (8 GB system mem) server running our AVG service and Hyper-v. Under Hyper-v we have two guests, an XP Pro running old version of Quickbooks for company accounting and a Win2003R2 server running BlackBerry Enterprise Server running 4 blackberry phones. We have 10 real Vista Business workstations which will go Windows 7 Pro over the next two weeks. This all runs on 192.168.8.x and uses one public static IP address. We also have a ...

Uninstall old v. before downloading new?
I have tried the free trial Money Plus Deluxe on one computer, and now want to purchase and download to another computer which has Money 06 Premium loaded. Should I uninstall Money 06 Premium before downloading Plus Deluxe (after making a backup of the data, of course)? Is it OK to have both versions installed at the same time, at least until I have time to make sure everything has transfered correctly? Thanks for your help. In microsoft.public.money, Elizabeth wrote: >I have tried the free trial Money Plus Deluxe on one computer, and now want >to purchase and download to a...

I-v
does anyone know what this means, is it a word term ? No, and without a context with which to evaluate your post, it is meaningless. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, lizzy asked: | does anyone know what this means, is it a word term ? ...

COUNTIF and Substring
Can the COUNTIF function be used with another function such as FIND o SEARCH ? What I want to do is search a range of cells for a certai sub-string and count the cell if it contains that sub-string. EXAMPLE: Cell A1 "Senior Analyst" Cell A2 "Junior Analyst" Cell A3 "Programmer" Cell A4 "Senior Analyst" Cell A5 "Web Developer" I want to count the number of cells in Column A that contain the wor "Analyst" Rolli -- Message posted from http://www.ExcelForum.com One way: =COUNTIF(A:A,"*A...

V CARD
How can I import an outlook v-card/vcard into CRM easily. WHen i drag and drop the vcard into my "Contacts in CRM" folder, it does not show up. ...

Volatile UDF v ws_change conflict
Hi all In a worksheet there are cells that call an Application.Volatile UDF. The UDF does nothing other than return the rowheight of the cell that calls it. That worksheet also has a Worksheet_Change() event handler. If I delete the cells that call the UDF then the Worksheet_Change() routine works as planned. If I un-volatile the UDF then the Worksheet_Change() routine works as planned. But if I leave the UDF in the worksheet and leave it volatile (required) then the Worksheet_Change() event misbehaves. I shall post the full code of the UDF and the Worksheet_Change() if it would help, but I...

Advanced Lookups setup should let me exclude inactive records
Advanced Lookups setup should let me exclude inactive accounts, customers, vendors and employees as well as discontinued items. There should be an option in the lookup windows to include these inactive/ discontinued master records if desired. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree"...

CListCtrl, Right clik-->Copy to clipboard-->Ctrl-V
Does anyone how to implement "Right Click-->Copy to Clipboard-->Ctrl-V" with CListCtrl control. I would like the functionality of copying the content of a CListCtrl into Word editor. Thanks, cy163 -- cy163cy163 ------------------------------------------------------------------------ Posted via http://www.codecomments.com ------------------------------------------------------------------------ for(int i = 0; i < ctl.GetCount(); i++) // or GetItemCount, I forget { ...form string based on elements in CListCtrl } Write string as CF_TEXT. Note that i...

Numbers V Text
I want to print out a document but I don't want it all printed out in numbers I want the text printed out. Here is what I don't want it say 223, I want to say =SUM(C7:C11)/15 I am sure it can be done via "Options" but I can't work out how. Jim Ducat Toggle the view to Formula View - Ctrl + ` (that's the same key as the tilde ~) MRO "James Ducat" <jd007f1618@blueyonder.co.uk> wrote in message news:x627b.16604$qK6.394@news-binary.blueyonder.co.uk... > I want to print out a document but I don't want it all printed out in > numbers I wan...

Lookup? #2
Workbook 1 Associate ABRAMS,TANJA BAILEY, NAKEYTA BAIROS, VERA BAKER,JANINE BANDISON,LINA Workbook 2 Associate Orders Avg $$ Total % ABRAMS,TANJA 52 52.40 5.77 BAILEY, NAKEYTA 83 62.97 26.51 BAIROS, VERA 105 55.19 9.52 BAKER,JANINE 88 61.41 14.77 BANDISON,LINA 138 74.93 6.52 BARRY,DIANE 75 66.19 6.67 BEAUBRUN,CHERLINE 84 57.30 7.14 BELL,CHERYL 44 64.95 9.09 I want to open a master sheet (Workbook 1) and search for someones name in Workbook 2. If there name ...

Search Column & Row using CMap, Lookup
hi all , iam reading a .dat file with the following data pattern . 3001, 200 ,4005 ,5006 000 , 47.12 ,785 ,699 the first column is the key value , for ex - 3001 is the key . if i search for the key value( 3001) , and if it is found , i need to extract the data value at 3rd Column of the row ( 4005). can someone give an example ( using CMap, Lookup ) . This is pretty simple. First, create a structure class Data { public: UINT key; TYPE_OF_YOUR_CHOICE col1; TYPE_OF_YOUR_CHOICE col2; TYPE_OF_YOUR_CHOICE col2; }; Read the line, parse out the values, and store them...

Installing new copy of Office Mac v.x: Delete old one first?
I had the same serial # of Office v.x installed on 2 Macs on my home network, so we could use it at either location (but only a single location at a time). Now with my wife teaching fulltime we both need to use Word and PowerPoint a lot, often concurrently. We purchased a second copy of Office, the Academic version this time, and I am trying to find out -- before starting -- whether I need to completely uninstall the earlier Office suite ( and the troublesome serial number) before I try to install the new Academic version. It doesn't appear that Office ships with a de facto uninstaller pr...

DPM 2010 with Hyper-V Live Migration Cluster protection and MSA200
Hello Everybody, we started trying the DPM2010 to protect our Hyper-V R2 "live migration" Environement. Our architecture is a simple 2 node cluster with an attached HP MSA2000 unit. we encountered some difficulties in the backup process, I'll try to explain: When starting the Virtual Machine backup using the child partition snapshot we got some errors in the Windows Event Log like The first is an error generated by CAPIVSSProvider (I think the hardware VSS provider of HP): capiCreateSnapshots() : Erreur Capi 0x000000BA (Error: CAPI_ERROR_EXCEED_MAX_SNAPS...

V
Hi all, How do you assign a symbol/name to a cell? E.g. instead of =if(G10>1,"Yes", "no") , change it as =if(L>1,"Yes", "no"). -- Air Lancer Select G10. Enter "L" (without quotes) in the Name box at the left of the Formula Bar. In article <40250085-8000-448B-A1D2-FD2050AB444C@microsoft.com>, "Air Lancer" <chesserhawk-email.yahoo.com.sg> wrote: > How do you assign a symbol/name to a cell? > > E.g. > > instead of =if(G10>1,"Yes", "no") , change it as =if(L>1,...

Filtering a lookup field
Hi everyone, Has anyone looked at filtering lookup fields? Essentially want i want to do is filter the primary contact on the business form [crm 3.0] so that it only shows the clients of that business, rather than everyone. Is that possible? Also, is it possible to add other lookups so that there could be secondary contact, billing contact, manager contact... etc? Thanks, Jack ------=_NextPart_0001_10CB3D68 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Jack~ You won't be able to do this with CRM out of the box. However, you may be able to do this with SDK. I would t...

lookup i think!
Hi I am trying to set up a simple function. i want a list of paints see example below. I have managed to create a list using a validation function. What i can't seem to do is to display a price to the right of the item. So when i select an item from the list it picks a price which i store in the same workbook as the products list and it should appear in the cell to the right. For example if someone picks red paint then the price for that appears in the same row, one or two rows to the right. I want to link the product with a price. red paint 10.00 blue paint ...

countif on multiple selections
Can you use COUNTIF on multiple ranges of cells? eg c12:d17 g77:G99 d55:d88 and so on Thanks in advance Diane Hi I would combine multiple COUNTIFs >-----Original Message----- >Can you use COUNTIF on multiple ranges of cells? eg >c12:d17 g77:G99 d55:d88 and so on > >Thanks in advance > >Diane >. > how would I do that? with an OR statement? >-----Original Message----- >Hi >I would combine multiple COUNTIFs > >>-----Original Message----- >>Can you use COUNTIF on multiple ranges of cells? eg >>c12:d17 g77:G99 d55:d...

How to retrieve values from a lookup table
I want to default the OwnerID of a Case based on the OwnerID of the selected Customer. I know how to retrieve the typename, name and GUID from a LookUp object (in this case the CustomerID) but how can I retrieve associated values from the related table? Hi, You need to write a webservice to fetch the owner of the customer and than use that webservice withs JScript in onLoad of the case to set the owner of the case. -- PLEASE do click on Yes or No button if this post was helpful or not for our feedback. uMar Khan Email for direct contact: imumar at gmail dot com "Agile CRM...

DPM 2010, Server 2008, Hyper-V, Error 31313
I run a Windows Server 2008 with runs Hyper-V Role and DPM 2010 RC Server. If I add a local Hyper-V to a protection group, I get an error to install kb948465 (SP2) and kb971394. SP2 was already installed, so I installed kb971394. After server reebot I am still not able to add local hyper-v guest to protection group. I have read some posts about this error, but mostly them occured in cluster environments I dont have. Any hints? Thank you... Hi, Please install the RTM bits of DPM 2010 which contain a fix for the scenario when protecting local hyper-v guests. Thanks, -...