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

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

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

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

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