Two Criteria = 1

[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.

 

[RagDyer]

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,"")

 

[Pete_UK]

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

 

[Jazz]

This is superb! Thank you.

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,"")

 

[Jazz]

Thank you. This is excellent.

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

 

[Jazz]

This is very good. I am lucky that you helped me.

 

[Jazz]

This has been very helpful. Thank you for sharing your knowledge with me.