IF / SUMPRODUCT HELP

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
Click to expand...

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
 
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.
Click to expand...

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

Rick
 
Back
Top