Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
Hi,
I'm using sumproduct to count occurences for multiple criteria, but have a question... How can I use it for the following situation: A B C 1 Y N Y 2 Y N N 3 N N N 4 Y Y Y I'm looking to count every occurence of one or more Y. Cheers Marc |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
Hi Marc
You could use =COUNTIF(A1:C4,"Y") HTH John "Marc T" wrote in message ... Hi, I'm using sumproduct to count occurences for multiple criteria, but have a question... How can I use it for the following situation: A B C 1 Y N Y 2 Y N N 3 N N N 4 Y Y Y I'm looking to count every occurence of one or more Y. Cheers Marc |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
Hi John,
Thanksfor the reply, but what I'm needing to count is the number of rows that contain one or more Y rather than every Y that occurs. Marc "John" wrote: Hi Marc You could use =COUNTIF(A1:C4,"Y") HTH John "Marc T" wrote in message ... Hi, I'm using sumproduct to count occurences for multiple criteria, but have a question... How can I use it for the following situation: A B C 1 Y N Y 2 Y N N 3 N N N 4 Y Y Y I'm looking to count every occurence of one or more Y. Cheers Marc |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
"Marc T" wrote:
what I'm needing to count is the number of rows that contain one or more Y rather than every Y that occurs. One way: =SUMPRODUCT(--((A1:A4="y")+(B1:B4="y")+(C1:C4="y")0)) In all of your examples, column A is "y", which might allow some incorrect formulas seem to work when they really don't. You should also test with an example where column A is not "y", but one or more other columns is. ----- original message ----- "Marc T" wrote in message ... Hi John, Thanksfor the reply, but what I'm needing to count is the number of rows that contain one or more Y rather than every Y that occurs. Marc "John" wrote: Hi Marc You could use =COUNTIF(A1:C4,"Y") HTH John "Marc T" wrote in message ... Hi, I'm using sumproduct to count occurences for multiple criteria, but have a question... How can I use it for the following situation: A B C 1 Y N Y 2 Y N N 3 N N N 4 Y Y Y I'm looking to count every occurence of one or more Y. Cheers Marc |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct query
One way
Sub countrowsif() mc = "j" For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row If Application.CountIf(Rows(i), "=" & "y") 0 Then ms = ms + 1 End If Next i MsgBox ms End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Marc T" wrote in message ... Hi, I'm using sumproduct to count occurences for multiple criteria, but have a question... How can I use it for the following situation: A B C 1 Y N Y 2 Y N N 3 N N N 4 Y Y Y I'm looking to count every occurence of one or more Y. Cheers Marc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct query | Excel Worksheet Functions | |||
SUMPRODUCT query | Excel Discussion (Misc queries) | |||
Sumproduct query | Excel Discussion (Misc queries) | |||
SUMPRODUCT IF query | Excel Discussion (Misc queries) | |||
SUMPRODUCT Query | Excel Discussion (Misc queries) |