Sumproduct query

  • Thread starter Thread starter Marc T
  • Start date Start date
M

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
 
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
 
Marc T said:
what I'm needing to count is the number of rows that
contain one or more Y rather than every Y that occurs.
Click to expand...

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