|
Two Criteria = 1
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. |
Two Criteria = 1
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,"") -- David Biddulph Jazz wrote: 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. |
Two Criteria = 1
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,"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jazz" wrote in message ... 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. |
Two Criteria = 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 On Aug 14, 11:06*pm, Jazz wrote: 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. * * |
Two Criteria = 1
Jazz wrote:
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 ,"") |
Two Criteria = 1
This is superb! Thank you.
"David Biddulph" wrote: 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,"") -- David Biddulph Jazz wrote: 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. |
Two Criteria = 1
Thank you. This is excellent.
"Pete_UK" wrote: 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 On Aug 14, 11:06 pm, Jazz wrote: 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. |
Two Criteria = 1
This has been very helpful. Thank you for sharing your knowledge with me.
"smartin" wrote: Jazz wrote: 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 ,"") |
| All times are GMT +1. The time now is 10:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com