|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 11th, 2010, 01:01 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. 
|
|
#2
February 11th, 2010, 01:46 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Vaules in a range that appear in another range
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.
|
|
#3
February 11th, 2010, 01:55 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Vaules in a range that appear in another range
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.
|
|
#4
February 11th, 2010, 03:40 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Vaules in a range that appear in another range
=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.
|
|
#5
February 11th, 2010, 04:45 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Vaules in a range that appear in another range
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.
|
|
#6
February 11th, 2010, 05:08 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count Vaules in a range that appear in another range
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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
