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.

 

[David Biddulph]

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

 

[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

 

[smartin]

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.

Either of equivalent formulas might work:

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

A couple comments:

Not sure why you represented your formula with {curly braces}. In Excel
formulas, {braces} designate an array or array formula, which you do not
need here. I suspect you didn't really mean you were using arrays, in
which case I recommend you do not use {braces} in your examples -- it
might confuse things (^:

You have asked to check whether K4 ="10". "Double quotes" also have a
special meaning in Excel formulas: they indicate the value is treated as
text rather than the number 10. This can not only confuse things, it
will cause your formulas not to work if you incorrectly quote something
that is actually a number (or vice versa). With this in mind you might
need to use one of these formulas instead:

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

 

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