IF / SUMPRODUCT HELP
 

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={"Activ e","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.

IF / SUMPRODUCT HELP
 

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={"Activ e","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

IF / SUMPRODUCT HELP
 

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


"Grunt" wrote:

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={"Activ e","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.

IF / SUMPRODUCT HELP
 

Current Formula
=IF(($G9="Full")*AND($H9="Perm"),IF(OR($I9={"Activ e","Detail In"}),1,0))

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

IF / SUMPRODUCT HELP
 

Thank You, this solved my problem.

"Rick Rothstein (MVP - VB)" wrote:

Current Formula
=IF(($G9="Full")*AND($H9="Perm"),IF(OR($I9={"Activ e","Detail In"}),1,0))

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

IF / SUMPRODUCT HELP
 

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

"Teethless mama" wrote:

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


"Grunt" wrote:

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={"Activ e","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.