| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF
I can't find anything in the help. I need to be to count more than one
criteria. Is this possible? As an example. A B 1 New York Baseball 2 New York Football 3 Cincinnati Baseball 4 Cleveland Baseball I want to be able to display this in another workbook B C Baseball Football 1 New York 1 1 2 Cincinnati 1 0 3 Cleveland 1 0 So as an example, what formula can I use in cell B1 to count both criteria, both New York and baseball. Thanks in advance Doug |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF
=sumproduct((a2:a200="New York")*(b2:b200={"Baseball","Football"}))
-- Don Guillett SalesAid Software "D VanD" wrote in message ... I can't find anything in the help. I need to be to count more than one criteria. Is this possible? As an example. A B 1 New York Baseball 2 New York Football 3 Cincinnati Baseball 4 Cleveland Baseball I want to be able to display this in another workbook B C Baseball Football 1 New York 1 1 2 Cincinnati 1 0 3 Cleveland 1 0 So as an example, what formula can I use in cell B1 to count both criteria, both New York and baseball. Thanks in advance Doug |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF
Why not use a Pivot Table?
-- Olly "D VanD" wrote in message ... I can't find anything in the help. I need to be to count more than one criteria. Is this possible? As an example. A B 1 New York Baseball 2 New York Football 3 Cincinnati Baseball 4 Cleveland Baseball I want to be able to display this in another workbook B C Baseball Football 1 New York 1 1 2 Cincinnati 1 0 3 Cleveland 1 0 So as an example, what formula can I use in cell B1 to count both criteria, both New York and baseball. Thanks in advance Doug |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF
Oops, I read too quickly. TWO formulas
=sumproduct((sheet1!$a$2:$a$200="New York")*(sheet1!$b$2:$b$200="Football") for one & just change the football to baseball for the other. -- Don Guillett SalesAid Software "D VanD" wrote in message ... I can't find anything in the help. I need to be to count more than one criteria. Is this possible? As an example. A B 1 New York Baseball 2 New York Football 3 Cincinnati Baseball 4 Cleveland Baseball I want to be able to display this in another workbook B C Baseball Football 1 New York 1 1 2 Cincinnati 1 0 3 Cleveland 1 0 So as an example, what formula can I use in cell B1 to count both criteria, both New York and baseball. Thanks in advance Doug |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF
Awesome. Thanks Don.
"Don Guillett" wrote in message ... Oops, I read too quickly. TWO formulas =sumproduct((sheet1!$a$2:$a$200="New York")*(sheet1!$b$2:$b$200="Football") for one & just change the football to baseball for the other. -- Don Guillett SalesAid Software "D VanD" wrote in message ... I can't find anything in the help. I need to be to count more than one criteria. Is this possible? As an example. A B 1 New York Baseball 2 New York Football 3 Cincinnati Baseball 4 Cleveland Baseball I want to be able to display this in another workbook B C Baseball Football 1 New York 1 1 2 Cincinnati 1 0 3 Cleveland 1 0 So as an example, what formula can I use in cell B1 to count both criteria, both New York and baseball. Thanks in advance Doug |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF
To ensure drag-ability for the formula, use
=SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$A$2: $A$100=B$1) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "D VanD" wrote in message ... I can't find anything in the help. I need to be to count more than one criteria. Is this possible? As an example. A B 1 New York Baseball 2 New York Football 3 Cincinnati Baseball 4 Cleveland Baseball I want to be able to display this in another workbook B C Baseball Football 1 New York 1 1 2 Cincinnati 1 0 3 Cleveland 1 0 So as an example, what formula can I use in cell B1 to count both criteria, both New York and baseball. Thanks in advance Doug |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF
One last question!!!
I have this formula =SUMPRODUCT((KOP!$C$2:$C$40000=$A10)*(KOP!$B$2:$B$ 40000="81-AOL ")*(KOP!$A$2:$A$40000=DATEVALUE("02/01/2004"))) This works fine I want to be able to populate the DATEVALUE("02/01/2004") from a cell value rather than having to change the formula each month. I tried this but it didn't work Cell A3 is set to 02/01/2004 =SUMPRODUCT((KOP!$C$2:$C$40000=$A9)*(KOP!$B$2:$B$4 0000="81-AOL ")*(KOP!$A$2:$A$40000=DATEVALUE(A3))) Thanks "Bob Phillips" wrote in message ... To ensure drag-ability for the formula, use =SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$A$2: $A$100=B$1) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "D VanD" wrote in message ... I can't find anything in the help. I need to be to count more than one criteria. Is this possible? As an example. A B 1 New York Baseball 2 New York Football 3 Cincinnati Baseball 4 Cleveland Baseball I want to be able to display this in another workbook B C Baseball Football 1 New York 1 1 2 Cincinnati 1 0 3 Cleveland 1 0 So as an example, what formula can I use in cell B1 to count both criteria, both New York and baseball. Thanks in advance Doug |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
COUNTIF
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
| edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
| countif | Excel Worksheet Functions | |||
| COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Hybrid Mode
