Count entries with 3 or more conditions

[bk1983]

I have a sheet which lists out the percentage of access for different fields , and whether or not a field is active. For example:

Access Percent Active
22 yes
55 no
97 no
12 yes
87 yes
44 yes
65 yes

now I need to get the results for the following:

Number of fields which are active and have access percentage between 35 and 70. For example in this case the answer should be 2.

I have tried using COUNT IF, SUMPRODUCT...and several combinations thereof, all to no avail. Please help

[:) Sixthsense :)]

Try this…

=SUMPRODUCT((A1:A7>=35)*(A1:A7<=70)*(B1:B7>="Yes"))

[benishiryo]

welcome to the forum bk1983. try:
=SUMPRODUCT((B2:B8="yes")*(A2:A8>=35)*(A2:A8<=70))

[Fotis1991]

Hi

Try this.

Please Login or Register  to view this content.

[FDibbins]

Hi and welcome to the forum

If you do have 2003 (as your profile says), use the sumproduct() suggested above. If you have 2007 or later (pleas update your profile), you could look at using the COUNTIFS() function

=COUNTIFS(A2:A20,">=35",A2:A20,"<=70",B2:B20,"yes")

[bk1983]

Actually I had tried it before, but hadn't worked. Weird. Guess I didn't put the second set of brackets. Thanks to you both guys

[bk1983]

Thank. That has helped.