My daughter has spread sheet with two columns, C and F. She wants to
compute the number of rows in which the test criteria is satisfied in
both columns C and F. The following formula returns a “1” if the test
criteria is met in both, and a zero if one or the other criteria is not
met.

=(C3="+/+")*(F3="blast")

My thought was to construct the array formula:

{=SUM(C3:C19="+/+")*(F3:F19="blast")}

but it returns zero, the wrong answer.

We could add more columns and then sum the columns, but it gets messy
because there are numerous combinations to be checked.

We’ve also tried various configurations of SumIF and Count.

Suggestions?

Try this:
=sumproduct(--(c3:c19="+/+"),--(f3:f19="blast"))
--
Jim


"windsurferLA" wrote:


My daughter has spread sheet with two columns, C and F. She wants to
compute the number of rows in which the test criteria is satisfied in
both columns C and F. The following formula returns a €œ1€� if the test
criteria is met in both, and a zero if one or the other criteria is not
met.

=(C3="+/+")*(F3="blast")

My thought was to construct the array formula:

{=SUM(C3:C19="+/+")*(F3:F19="blast")}

but it returns zero, the wrong answer.

We could add more columns and then sum the columns, but it gets messy
because there are numerous combinations to be checked.

Weve also tried various configurations of SumIF and Count.

Suggestions?

Thanks.... I don't know why I didn't think of that... it works.

JBoulton wrote:
Try this:
=sumproduct(--(c3:c19="+/+"),--(f3:f19="blast"))