Sumproduct query

[Marc T]

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

 

[John]

Hi Marc
You could use =COUNTIF(A1:C4,"Y")
HTH
John

 

[Marc T]

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

 

[JoeU2004]

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 -----

 

[Don Guillett]

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