#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 45
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default 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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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. * *


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 915
Default 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 ,"")


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 45
Default 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.




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 45
Default 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.



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 45
Default 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 ,"")

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Query criteria based on a changing criteria list bwilk77 Excel Discussion (Misc queries) 2 May 27th 09 04:03 PM
Count how many criteria in a column match criteria in another colu Charles Stover Excel Discussion (Misc queries) 3 March 6th 09 09:39 PM
EXCEL - Meet 2 criteria, then find next case of third criteria Elaine Excel Worksheet Functions 3 December 1st 08 11:43 PM
Counting Cells with multiple criteria.One criteria supporting wild Azhar Saleem Excel Worksheet Functions 1 January 12th 05 11:54 AM
Counting Cells with multiple criteria.One criteria supporting wild Azhar Arain Excel Worksheet Functions 1 January 12th 05 09:33 AM


All times are GMT +1. The time now is 09:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"