Count Vaules in a range that appear in another range

I have three ranges e.g.

Range 1      Range 2     Range 3
Bread         Apples        Bread
Apples        Oranges     Buns
Oranges      Pears
Buns
Bread
Pears
Apples

I'm trying to construct a formula that counts the number of times, say, and 
entry in Range 3 appears in Range 1  - In this instance the answer would be 3.
0
Utf
2/11/2010 1:01:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
566 Views

Similar Articles

[PageSpeed] 28

Hi,
try

=SUMPRODUCT(($A$2:$A$100=c2)+($A$2:$A$100=c3))

Being a2 to a100 range 1 and c2 and c3 products in range 3

"T Newbery" wrote:


> I have three ranges e.g.
> 
> Range 1      Range 2     Range 3
> Bread         Apples        Bread
> Apples        Oranges     Buns
> Oranges      Pears
> Buns
> Bread
> Pears
> Apples
> 
> I'm trying to construct a formula that counts the number of times, say, and 
> entry in Range 3 appears in Range 1  - In this instance the answer would be 3.
0
Utf
2/11/2010 1:46:09 PM
Thanks, but is there a way to do this without enumerating everything in range 
3. It's basically a lookup list that changes over time. I only want to change 
the list, not everything that references it...

"Eduardo" wrote:

> Hi,
> try
> 
> =SUMPRODUCT(($A$2:$A$100=c2)+($A$2:$A$100=c3))
> 
> Being a2 to a100 range 1 and c2 and c3 products in range 3
> 
> "T Newbery" wrote:
> 
> 
> > I have three ranges e.g.
> > 
> > Range 1      Range 2     Range 3
> > Bread         Apples        Bread
> > Apples        Oranges     Buns
> > Oranges      Pears
> > Buns
> > Bread
> > Pears
> > Apples
> > 
> > I'm trying to construct a formula that counts the number of times, say, and 
> > entry in Range 3 appears in Range 1  - In this instance the answer would be 3.
0
Utf
2/11/2010 1:55:01 PM
=SUMPRODUCT(COUNTIF(A1:A100,C1:C100))


"T Newbery" wrote:

> Thanks, but is there a way to do this without enumerating everything in range 
> 3. It's basically a lookup list that changes over time. I only want to change 
> the list, not everything that references it...
> 
> "Eduardo" wrote:
> 
> > Hi,
> > try
> > 
> > =SUMPRODUCT(($A$2:$A$100=c2)+($A$2:$A$100=c3))
> > 
> > Being a2 to a100 range 1 and c2 and c3 products in range 3
> > 
> > "T Newbery" wrote:
> > 
> > 
> > > I have three ranges e.g.
> > > 
> > > Range 1      Range 2     Range 3
> > > Bread         Apples        Bread
> > > Apples        Oranges     Buns
> > > Oranges      Pears
> > > Buns
> > > Bread
> > > Pears
> > > Apples
> > > 
> > > I'm trying to construct a formula that counts the number of times, say, and 
> > > entry in Range 3 appears in Range 1  - In this instance the answer would be 3.
0
Utf
2/11/2010 3:40:05 PM
Hi,

I assume your range to be counted is in column B

=SUMPRODUCT(COUNTIF(A1:A100,B1:B100))

"T Newbery" wrote:

> Thanks, but is there a way to do this without enumerating everything in range 
> 3. It's basically a lookup list that changes over time. I only want to change 
> the list, not everything that references it...
> 
> "Eduardo" wrote:
> 
> > Hi,
> > try
> > 
> > =SUMPRODUCT(($A$2:$A$100=c2)+($A$2:$A$100=c3))
> > 
> > Being a2 to a100 range 1 and c2 and c3 products in range 3
> > 
> > "T Newbery" wrote:
> > 
> > 
> > > I have three ranges e.g.
> > > 
> > > Range 1      Range 2     Range 3
> > > Bread         Apples        Bread
> > > Apples        Oranges     Buns
> > > Oranges      Pears
> > > Buns
> > > Bread
> > > Pears
> > > Apples
> > > 
> > > I'm trying to construct a formula that counts the number of times, say, and 
> > > entry in Range 3 appears in Range 1  - In this instance the answer would be 3.
0
Utf
2/11/2010 4:45:01 PM
Thanks, that's exactly what I need.

I'm not even going to pretend I know why that works when COUNTIF by itself 
doesn't...

"Teethless mama" wrote:

> =SUMPRODUCT(COUNTIF(A1:A100,C1:C100))
> 
> 
> "T Newbery" wrote:
> 
> > Thanks, but is there a way to do this without enumerating everything in range 
> > 3. It's basically a lookup list that changes over time. I only want to change 
> > the list, not everything that references it...
> > 
> > "Eduardo" wrote:
> > 
> > > Hi,
> > > try
> > > 
> > > =SUMPRODUCT(($A$2:$A$100=c2)+($A$2:$A$100=c3))
> > > 
> > > Being a2 to a100 range 1 and c2 and c3 products in range 3
> > > 
> > > "T Newbery" wrote:
> > > 
> > > 
> > > > I have three ranges e.g.
> > > > 
> > > > Range 1      Range 2     Range 3
> > > > Bread         Apples        Bread
> > > > Apples        Oranges     Buns
> > > > Oranges      Pears
> > > > Buns
> > > > Bread
> > > > Pears
> > > > Apples
> > > > 
> > > > I'm trying to construct a formula that counts the number of times, say, and 
> > > > entry in Range 3 appears in Range 1  - In this instance the answer would be 3.
0
Utf
2/11/2010 5:08:01 PM
Reply:

Similar Artilces:

Embedding an application within another application
I want to be able to start an application from my VC++ application & have the application dock its window inside my VC++ application. How can this be done? Thanks Unless the application you are starting is an OLE Server app, you can't. It is meaningless to think about a general application embedding in any way in another application. To run an OLE server app, you have to be an OLE client app. This is nontrivial, but doable. joe On Wed, 12 Jan 2005 07:59:06 -0800, "rgarf" <rgarf@discussions.microsoft.com> wrote: >I want to be able to start an application fro...

How to count the number of non blank cells in a filtered list
There must be a way of doing this, but I cannot see it. I have a spreadsheet with items to be progressed. This list can be filtered on a number of criteria. The filter currently only works on the first 1000 rows. It is not considered likely that it will ever go above this. When I filter, Excel tells me there are "x of y records found" where x is all the filtered items plus any blank rows left until row 999; and y is the number of records in total (minus any header rows). How can I get Excel to just give me the number of filtered rows (minus the blank ones). What I am basical...

appearance on windows2000 and windowsXP
I find some control, e.g: button,listctrl,is sensitive to mouse on WinXP. I run a software on WinXP,e.g: Outlook express.When the mouse move into the rectangle of the button or listctrl,the control face will be changed,and it's beautiful. Then I run it on Windows2000,its face don't change when the mouse move into its rectangle. the action default by windows2000 and winxp. I create a project using vc6,and build it on windows2000 and winxp. It's pity they aren't sensitive to mouse both winxp and win2000. I know I can redraw the control to do them,but it's troubled.I only wa...

Print Range
I have made a macro to print a range in Excel. All it does is print a few lines and dots at the top Range("D4:G11").Select ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)" Is there another way of printing that range as a speadsheet or modifying the above macro to print the range in a viewable form. Try Range("D4:G11").PrintOut Copies:=1 -- Jacob "Philosophaie" wrote: > I have made a macro to print a range in Excel. All it does is print a few > lines and dots at the top > > > Range(&qu...

Count my Customers
Hi. I use my spreadsheet as a database containing some 15,000 transactions. The fields include Customer No. in column 'L' and a Period field in column 'C'. The Customer No. may be alphabetical, numeric or alphanumeric. The period is the year. An individual customer may have anything from 1 to 50 transactions in the database which is sorted by Customer No. With this forums help I have successfully counted the number of transactions by period, and totalled the value by period. I would now like to count the total number of different customers I have within the d...

drop down box leading to another drop down box
I have a cell containing a drop down box with three options. Can i design my worksheet so that when one option is selected a drop down box in the neighbouring cell appears with a number of options in it? Each of the original 3 options in the first drop down box needs to lead to a different list in the second drop down box, though the options can be repeated. Any help would be much appreciated. Thanks stumakker, have a look here and see if this will work for you, http://www.contextures.com/xlDataVal02.html -- Paul B Always backup your data before trying something new Please post any r...

Referencing a cell from another worksheet
I am in cell F5 of worksheet "January" and am trying to get it to display what is in cell B1 of worksheet "Sheet1" within the same workbook. For some reason it won't show the value, it just shows me the formula i've typed in. I tried: ='Sheet1'!B1 (which i have in another document and it's working fine) ='Sheet1!'B1 =Sheet1!B1 Help! Occasionally Excel can be stubborn that way. Try an alternative method of entering the formula. Within teh cell where the formula is type the "=" then navigate to the worksheet and cell ...

How to count a coloum if two conditions are met
I want to count the number of entries in a column if its value = X but only if another column = Y, kind of like merging two countif statemnts so one countif is =COUNTIF(STATS!E:E,B3) where coloum B is a list of persons' initials and the second is =countif(STATS!I:I,"ONLINE") So I want to count the number of rows with "online" in colomn I, but only on row's where coloum E = the value in B3 I have tried the following formula {=COUNT(IF(STATS!E:E=B3,IF(STATS!I:I="ONLINE",STATS!I:I)))} as an array function, but it isn't working What...

Automate another app
I have been programming in VB (classic) for many years and am quite familiar with how I would accomplish this in that enviornment. However I just started using C# and have no clue. For starters in VB I would start the other application by calling the Shell function. Then to actually control the other app I would first locate the window handle for controls I wanted to interact with using Win32 APIs which allow me to retrieve the hWnd for a window given its ControlID (gathered from Spy++). I would then use the SendMessage API (with the appropriate message constant) to enter text in edi...

Re: "SUMIF" or "COUNTIF" to count alpha characters as .5 ?
small example, we need to count employees for SICK DAY ( S ) as 1 point, and a late or TARDY ( T ) as .5 point. A1 = S B1 = T it should total to 1.5 I tried to use: =COUNTIF(A1:B1, "S" +1) ( equals 1 ) and I tried: =COUNTIF(A1:B1, "T" + .5 ) ( equals .5 ) But neither worked.....I also tried the SUMIF but no luck..... Any Ideas??? Thanks so much, this is a great group! You can use the following formula to total the S and T values: =COUNTIF(A1:B1,"S")+COUNTIF(A1:B1,"T")*0.5 Ange...

Outlook Express Message count
Sorry for the double post. Background: By design (I think), Outlook Express 6 shows a count of unread messages on the left navigator, next to each folder name. The folders do not show the count of total or read messages next to the folder name. Thus, if all messages in a folder have been read, there won't be a numerical display next to the folder name at all. Now, if a user wants to see more message count information, and VIEW -> LAYOUT -> BASIC -> "Status Bar" is checked, the status bar at the bottom of Outlook Express window will appear, and display the total messag...

Transposing information from one sheet of a workbook to another
I'm trying to transpose information (names) from one worksheet to another within the same workbook. I can do it on the same worksheet by using the = sign then clicking on the box that i want to transpose. However when I attempt to do this in another worksheet I get an error that says #value. Any suggestions select the range you need, click copy, select the first cell on the other workbook you want the info in, click edit/paste special and check the transpose box. then ESC to clear the copy mode. "mjano25" wrote: > I'm trying to transpose information (names) f...

Subscript out of range in FRx
I'm trying to create a report in FRx 6.7. When I go to select the default for a particular company, I get the following error message: Error 9: Subscript out of range This is an unexpected error that is caused by some combination of events that were not encountered during testing. Please notify technical support of this error. To determine the cause, try to remove any recent changes made to report formats until the error disappears. Procedure: [PopFRLTables] I have two other companies set up and have no problem selecting any of them as the default. Any ideas? Thanks. -- Bob Thi...

getting data from one sheet to another
I am working on a labor chart. The first sheet has the names and day of the week. I have them formatted to just put in the number of hours and it figures pay. There are 4 different job descriptions and at the bottom of each day, it totals the dollars spent for labor on that day. My second sheet has sales and will divide the cost of labor by sales and give me a percentage. On the second page, how can I get the total dollars in labor for each catagory from sheet one to a certain cell on sheet 2 without manually entering it. I know it can be done as I have seen it before Assuming the da...

Multisheet range in SUMPRODUCT?
When I write SUM(Sheet1:Sheet4!B4:B6), that works. But if I change the function name to SUMPRODUCT, I get a #REF error. Copied from the Formula Bar: =SUMPRODUCT(Sheet1:Sheet4!B4:B6) And if I use that form of range in a subexpression, I get a #NAME error. Copied from the Formula Bar: =SUMPRODUCT(--(sheet1:Sheet4!B4:B6<=20)) Note that Sheet1 is lowercased by Excel. All my changes were made by successive minimum editing of the original SUM formula. Is there some way to make this kind of range work with SUMPRODUCT, short of the obvious, namely breaking this into mult...

Automatic range update of Graph
Hello, Can some one tell me if it is possible to automatically update the (date) ranges in a graph? Now I have to extend the ranges manually each time there is a new range provided. Thanx in advance!!! Regards, Robert Have a look at Tushar Mehta's Dynamic Ranges site. http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html Or Jon Peltier's site on Dynamic Charts http://peltiertech.com/Excel/Charts/index.html#hdrDyno Gord Dibben Excel MVP On 30 Jun 2005 06:59:31 -0700, p.strijbosch@chello.nl wrote: >Hello, > >Can some one tell me if it is possible to ...

Using a range in Sum Function
I have run across an oddity in Exel 2010 Trial Edition. If I define a range to be cells c1, d1, f1 and g1 using the Name Manager and then put the following in another cell: =Sum(range name), the function returns the proper value. BUT, each time I open the worksheet after just viewing it (not changing anything), Excel asks me if I want to save the changes. I didn't make any changes so why should it ask me that? Is there a solution to this? Thank you. Do you have something in a header or footer that deals with current time or date? -- JoAnn Paules MVP M...

Modifier
We recently acquired Modifier and have successfully modified acouple of forms (making fields required) and have added some VBA code to another window. Now I am attempting to modifiy another window, specifically the Purchasing Invoice Entry window. However, when I make this my current window and then go to Tools - Customize - Modify Current Window, I get the following message, "The Modifier is currently unavailable because another user is editing resources in the Forms Dictionary." I have tried it with other windows as the current window and get the same message. Similarly, ...

Embedded Chart does not appear on print preview
Please help! I have a worksheet with an embedded chart in it. The chart background is coloured blue and the pie chart within it is multi coloured. When I click on print preview I can see the worksheet with a blue box where the chart is but the multi-coloured pie chart has disappeared. So it is like the chart background is there but not the chart itself. Can anyone explain this? Kind regards & TIA Stuart :-) Select the chart, from Format menu, choose Selected Chart Area. On Properties tab, check the Print Object checkbox. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Tec...

Right Click Menu Does Not appear
When I right click on an email, the context menu does not appear. It will appear when multiple email are selected. ...

another Exchange question ... two SMTP domains in one AD domain
I have a test environment with two standard edition Exchange 2003 servers in one mixed mode Windows 2003 Active Directory domain. Each Exchange server has its own SMTP domain -- say a.company.com for Exchange Server A and b.company.com for Exchange Server B. The users are only going to use Exchange Server A or B depending on their physical location in proximity to the server. Exchange Server A and B are also domain controllers, where Exchange Server A is the global catalog server. When I add a user to AD, they get user@a.company.com and user@b.company.com as email addresses. I can delete ...

Drop-down data not appearing in exported XML
I'm exporting data as XML from an Excel spreadsheet. Cells with plain text in them work fine, but cells with a selection from a drop-down list do not. The XML file shows everything is there, except data from cells with drop-down selections. No error message generated, just no trace of the data. Any suggestions? thanks kc Fixed the problem. Found out that the drop-downs were actually objects placed on top of the cell, the options were not coded into the cells themselves. "Kristi" <kristi@NOSPAMinscriber.com> wrote in message news:MaCdnWD2xtF6CtveRVn-qQ@golden.net... > ...

vertical lines appear after send/receive email Outlook 2003
I've just installed Outlook 2003 on a new Acer Travelmate C301. When email is checked, at the very end of the send/receive process the screen changes. Tightly spaced vertical black lines appear within the Outlook window, extending exactly to the edge of the frame of the main panes on three sides. Oddly, the lines stop about 40% of the way up the pane so they only cover 40% of the space within the frame. If I switch to another program and then back again, the lines disappear until the send/receive process occurs again. If the program I switch to only refreshes a portion of the a...

Counting Selected/Highlighted Rows
Need to modify this maco to not count hidden rows. For example if seven rows of ten rows in question are hidden, I need the macro to count only the three visible on screen. Also, if no rows were hidden, and all ten were selected (highlighted), the macro should count ten. In other words, it should count only what is selected/visible on screen. Sub CountHighlightedRows() MsgBox "Rows Selected: " & Selection.Rows.Count End Sub Not sure if I understand, but try this: Sub fj() Dim x As Long x = Selection.SpecialCells(xlCellTypeVisible).Count MsgBox...

word count in Excel 2003
How can one get a word count in an Excel document? Traduc, If you have Word, paste the sheet into it, and use File - Properties - Statistics. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Traduc" <Traduc@discussions.microsoft.com> wrote in message news:37B364F5-2029-469A-87CA-D0BC82365718@microsoft.com... > How can one get a word count in an Excel document? Thanx "Traduc" wrote: > How can one get a word count in an Excel document? ...