Number of rows where two column cells have same value ?

[eagles396]

Hello

I've created the below vba code in Excel which returns the total number of rows where column D ="Y". What I would like to do is to find out the total number of rows where column D = "Y" and column E = "Y".


Code:

For Each cell In ActiveWorkbook.Sheets("Survey").Range("D1:D300")

If cell.Value = "Y" Then
CountD = CountD + 1
End If
Next cell

ThisWorkbook.Sheets("Results").Cells(7, 2) = CountD


All help is greatly appreciated

[Ron Coderre]

Is there a reason you can't just use a formula?

This formula returns the count of rows where Col_D and Col_E contain "Y"

=SUMPRODUCT((D1:D20="Y")*(E1:E20="Y"))

Or do you need to do that with VBA?

[eagles396]

Thanks for the formula. Do you know whats wrong with the following formula as I'm getting an error.

=(SUMPRODUCT((Survey!D1:D200="Y")*(Survey!F1:F200="Y"))) / SUMPRODUCT(Survey!D1:D200="Y")

[Ron Coderre]

Yes.....Here's the reason...

A boolean expression (fancy phrase for an expression that returns a True/False value) does NOT return a numeric result.

Consequently, this:
Survey!D1:D200="Y"

returns a series of True and False values.
SUMPRODUCT is trying to add them together...and failing.

However, you can coerce those values into proper numbers by forcing Excel to implicitly convert True to 1 and False to 0 by applying a mathematical operator to them.

The generally accepted approach by knowledgable users is this:
--(Survey!D1:D200="Y")

The first minus sign (-) makes True = -1 and False = 0.
The second minus sign converts those values to 1 and 0, respectively.

You could also multiply the values by 1, but that might mislead users. Whenever you see the double-minus sign in a formula, that's an indication that it's only purpose is to force a conversion to numbers.

So, you're formula could be:

Please Login or Register  to view this content.

BUT, it should be this:

Please Login or Register  to view this content.

Note: there are NO spaces in those formulas

Does that help?

[eagles396]

Thanks, it works perfectly