array formula count results of two tests

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

windsurferLA - 25 Jul 2006 22:38 GMT

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?

Reply to this Message

JBoulton - 25 Jul 2006 23:04 GMT

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

Signature

Jim

> 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
[quoted text clipped - 16 lines]
>
> Suggestions?

Reply to this Message

windsurferLA - 26 Jul 2006 00:33 GMT

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

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

Reply to this Message