Two Criteria = 1

  • Thread starter Thread starter Jazz
  • Start date Start date
J

Jazz

I am looking for help with a formula. Write now if Sheet1 has a “10†in cell
K4, and I put this formula {=IF('Sheet1'!K4="10",1,""} in cell P4 the number
“1†will be displayed in cell P4. I wanted to modify the formula so that if
cell J4 displays “Apples†and K4 displays “10†(assuming I put the formula in
P4 again) a “1†will be displayed in cell P4. Thank you for your ideas.
 
Is the "10" a number or a text entry?

Since you enclosed it with quotes in your example, I'll assume it's text.
If it *is a number*, just remove the quotes:

=If(And('Sheet1'!K4="10",'Sheet1'!J4="Apples"),1,"")

You might also try using dedicated cells to hold your variable criteria, so
that you could change the criteria without having to change the formula
itself.

Say A1 for the number,
and A2 for the fruit:

=If(And('Sheet1'!K4=A1,'Sheet1'!J4=A2),1,"")
 
Try this:

=IF(AND('Sheet1'!J4="Apples",'Sheet1'!K4="10"),1,"")

Note that by putting quotes around the 10 you are making Excel treat
it as a text value. If K4 contained the number 10 then the formula
would fail.

Note also that curly braces { and } have a special meaning in Excel
related to array formulae, so you should not use them - they'll just
cause confusion.

Hope this helps.

Pete
 
This is superb! Thank you.

David Biddulph said:
IF(AND('Sheet1'!K4="10",'Sheet1'!J4="Apples"),1,"")
or
IF(AND('Sheet1'!K4="10",J4="Apples"),1,"")
depending on whether your J4 is on sheet1 or on the current sheet.

Note that you are checking for K4 containing the text string "10"; if you
want to check for the number 10, change it to
IF(AND('Sheet1'!K4=10,'Sheet1'!J4="Apples"),1,"")
Click to expand...
 
Thank you. This is excellent.

Pete_UK said:
Try this:

=IF(AND('Sheet1'!J4="Apples",'Sheet1'!K4="10"),1,"")

Note that by putting quotes around the 10 you are making Excel treat
it as a text value. If K4 contained the number 10 then the formula
would fail.

Note also that curly braces { and } have a special meaning in Excel
related to array formulae, so you should not use them - they'll just
cause confusion.

Hope this helps.

Pete
Click to expand...
 
Back
Top