| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF - across 2 columns
I'm hoping somebody can help me, as I feel this formula should be simple, but
I can't get it to work! I have 2 columns A B 10 2008 11 2008 10 2009 I need to count how many time column A records the occurance of "10" when column B records the occurance of "2008". Please help! Beks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF - across 2 columns
Hi Rebekah,
Im sure there is a better way to do this but the below will work. In column c do: =and(A1=10,B1=2008). this will produce a true/False result. Then simply do a countif of on true in column C -- Rob Gaffney "Rebekah" wrote: I'm hoping somebody can help me, as I feel this formula should be simple, but I can't get it to work! I have 2 columns A B 10 2008 11 2008 10 2009 I need to count how many time column A records the occurance of "10" when column B records the occurance of "2008". Please help! Beks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF - across 2 columns
Of course, you could always do a count pivot table as well.
-- Rob Gaffney "Rebekah" wrote: I'm hoping somebody can help me, as I feel this formula should be simple, but I can't get it to work! I have 2 columns A B 10 2008 11 2008 10 2009 I need to count how many time column A records the occurance of "10" when column B records the occurance of "2008". Please help! Beks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF - across 2 columns
I have tried =sumproduct((range = "10")*(range = "2008))
This returned #VALUE Any other ideas? Your reply won't work because the formulas are running from another worksheet, and I the numbers relate to months and years... B "Gaffnr" wrote: Of course, you could always do a count pivot table as well. -- Rob Gaffney "Rebekah" wrote: I'm hoping somebody can help me, as I feel this formula should be simple, but I can't get it to work! I have 2 columns A B 10 2008 11 2008 10 2009 I need to count how many time column A records the occurance of "10" when column B records the occurance of "2008". Please help! Beks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF - across 2 columns
Hi,
try =SUMPRODUCT(--($A$8:$A$10=10),--($B$8:$B$10=2008)) change range to fit your needs "Rebekah" wrote: I have tried =sumproduct((range = "10")*(range = "2008)) This returned #VALUE Any other ideas? Your reply won't work because the formulas are running from another worksheet, and I the numbers relate to months and years... B "Gaffnr" wrote: Of course, you could always do a count pivot table as well. -- Rob Gaffney "Rebekah" wrote: I'm hoping somebody can help me, as I feel this formula should be simple, but I can't get it to work! I have 2 columns A B 10 2008 11 2008 10 2009 I need to count how many time column A records the occurance of "10" when column B records the occurance of "2008". Please help! Beks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF - across 2 columns
Hi Eduardo
Is it possible that I am getting the #VALUE result because the cells by which I am running the formula are already products of another formula? Many thanks "Eduardo" wrote: Hi, try =SUMPRODUCT(--($A$8:$A$10=10),--($B$8:$B$10=2008)) change range to fit your needs "Rebekah" wrote: I have tried =sumproduct((range = "10")*(range = "2008)) This returned #VALUE Any other ideas? Your reply won't work because the formulas are running from another worksheet, and I the numbers relate to months and years... B "Gaffnr" wrote: Of course, you could always do a count pivot table as well. -- Rob Gaffney "Rebekah" wrote: I'm hoping somebody can help me, as I feel this formula should be simple, but I can't get it to work! I have 2 columns A B 10 2008 11 2008 10 2009 I need to count how many time column A records the occurance of "10" when column B records the occurance of "2008". Please help! Beks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF - across 2 columns
I'm surprised that you got a #VALUE! error from that. I would have expected
to get an error reported when you tried to enter the formula, as the quote marks don't match. If you didn't actually use that formula, and you've mistyped it into the newsgroup, I would suggest that in future you don't try to retype. Copy from the formula bar and paste here. Secondly, are you sure that you intended to use the quote marks? Are you looking for 10 and 2008 as text strings or as numbers? What do =ISTEXT(A2) and =ISNUMBER(A2) show, and similarly for B2? Thirdly, your formula wouldn't work as you are using range as the name for both columns, and you asked for the 10 in one column and the 2008 in a different column. You need two different ranges. -- David Biddulph "Rebekah" wrote in message ... I have tried =sumproduct((range = "10")*(range = "2008)) This returned #VALUE Any other ideas? Your reply won't work because the formulas are running from another worksheet, and I the numbers relate to months and years... B "Gaffnr" wrote: Of course, you could always do a count pivot table as well. -- Rob Gaffney "Rebekah" wrote: I'm hoping somebody can help me, as I feel this formula should be simple, but I can't get it to work! I have 2 columns A B 10 2008 11 2008 10 2009 I need to count how many time column A records the occurance of "10" when column B records the occurance of "2008". Please help! Beks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF - across 2 columns
This worked for me....
Rob x =SUMPRODUCT(($A$1:$A$5=10)*($B$1:$B$5=2008)) -- Rob Gaffney "Rebekah" wrote: I'm hoping somebody can help me, as I feel this formula should be simple, but I can't get it to work! I have 2 columns A B 10 2008 11 2008 10 2009 I need to count how many time column A records the occurance of "10" when column B records the occurance of "2008". Please help! Beks |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF - across 2 columns
=SUMPRODUCT(--(A1:A100=10),--(B1:B100=2008))
Adjust ranges to suit. -- David Biddulph "Rebekah" wrote in message ... I'm hoping somebody can help me, as I feel this formula should be simple, but I can't get it to work! I have 2 columns A B 10 2008 11 2008 10 2009 I need to count how many time column A records the occurance of "10" when column B records the occurance of "2008". Please help! Beks |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF - across 2 columns
When you have multiple criteria use SUMPRODUCT()
=SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2)) 'In your case since it is in a different sheet...with month and year in F1 and F2 =SUMPRODUCT((Sheet2!A1:A10=F1)*(sheet2!B1:B10=F2)) If this post helps click Yes --------------- Jacob Skaria "Rebekah" wrote: I'm hoping somebody can help me, as I feel this formula should be simple, but I can't get it to work! I have 2 columns A B 10 2008 11 2008 10 2009 I need to count how many time column A records the occurance of "10" when column B records the occurance of "2008". Please help! Beks |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF - across 2 columns
=SUMPRODUCT((G1:G100={"letter","tabloid"})*(C1:C10 0*E1:E100)*{1,2})
"Rebekah" wrote: I'm hoping somebody can help me, as I feel this formula should be simple, but I can't get it to work! I have 2 columns A B 10 2008 11 2008 10 2009 I need to count how many time column A records the occurance of "10" when column B records the occurance of "2008". Please help! Beks |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF - across 2 columns
Sorry wrong post.
"Teethless mama" wrote: =SUMPRODUCT((G1:G100={"letter","tabloid"})*(C1:C10 0*E1:E100)*{1,2}) "Rebekah" wrote: I'm hoping somebody can help me, as I feel this formula should be simple, but I can't get it to work! I have 2 columns A B 10 2008 11 2008 10 2009 I need to count how many time column A records the occurance of "10" when column B records the occurance of "2008". Please help! Beks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif for 2 columns | Excel Worksheet Functions | |||
| countif in 2 columns | Excel Discussion (Misc queries) | |||
| Sum or Countif over Multiple Columns | Excel Worksheet Functions | |||
| Help with countif using two columns | Excel Worksheet Functions | |||
| Countif - Two Criteria in two columns are met. | Excel Discussion (Misc queries) | |||
Hybrid Mode
