IF / SUMPRODUCT HELP

[Guest]

I am using an IF statement to check a specific criteria but on some
responses I get a "FALSE" if none of the criteria is meant or 0 if part of
the criteria is meant. I would like to tigten this up and have a 1 or 0
response.
Current Formula
=IF(($G9="Full")*AND($H9="Perm"),IF(OR($I9={"Active","Detail In"}),1,0))

I've tried to use a sumproduct but column I has a possibility of 2 correct
entries and I'm not sure how to get the sumproduct to read all 3 columns and
the multiple choice possibility in column I.
Attempted Formula
=SUMPRODUCT(($G$8:$G$38="Full")*($H$8:$H$38="Perm")*($I8:I38={"Active","Detail In"}),(O8:O38))
The range on Column O being nothing but the number 1.

I appreciate any assitance.

 

[Rick Rothstein \(MVP - VB\)]

I am using an IF statement to check a specific criteria but on some

responses I get a "FALSE" if none of the criteria is meant or 0 if part of
the criteria is meant. I would like to tigten this up and have a 1 or 0
response.
Current Formula
=IF(($G9="Full")*AND($H9="Perm"),IF(OR($I9={"Active","Detail In"}),1,0))

I've tried to use a sumproduct but column I has a possibility of 2
correct
entries and I'm not sure how to get the sumproduct to read all 3 columns
and
the multiple choice possibility in column I.

Your formula above is missing the FALSE condition for the first IF function
call.... I think adding ',0' (comma-zero without the apostrophes) will do
what you want.

I have a question about your use of the AND function... if I am reading your
formula correctly, there is only one item in the AND function call, so why
are you using it? Did you perhaps mean to encase the ($G9="Full") inside it
(instead of performing the multiplication)?

Rick

 

[Guest]

Try this:
=SUMPRODUCT((G8:G38="Full")*(H8:H38="Perm")*(I8:I38={"Active","Detail In"}))

 

[Rick Rothstein \(MVP - VB\)]

Current Formula

Your formula above is missing the FALSE condition for the first IF
function call.... I think adding ',0' (comma-zero without the apostrophes)
will do what you want.

I meant to include... "adding it in front of the **last** closing
parenthesis".

Rick

 

[Guest]

Thank You, this solved my problem.

I meant to include... "adding it in front of the **last** closing
parenthesis".

Rick

 

[Guest]

Thanks, this formula now works and I appreciate the responses.