• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If formula - criteria in three cells

E

Eben

New Member
I need to get the If formula using criteria depending on three cells.

One of the three cells will always have a value of more than 0 and the other two will be 0.

I tried =IFF(AND(A1>0,B1=0,C1=0),G10,IF(AND(A1=0,B1>0,C1=0),H11,IF(AND(A1=0,B1=0,C1>0),I11,"N/A")))

I always get the N/A as a answer, even if say A1>0


Plse help
 
If you just need to find which one has a 1, then you should be able to do a lookup formula. I'm assuming the N/A only needs to appear if none of the 3 cells have a 1.

=IF(SUM(A1:C1),LOOKUP(1,A1:C1,G11:H11),"N/A")
 
Hi, Eben!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Try this as condition for your IF statement:

=SUMAPRODUCTO($A1:$C1;SIGNO($A1:$C1)) -----> in english: =SUMPRODUCT($A1:$C1,SIGN($A1:$C1))


Regards!


EDIT:

=SUMAPRODUCTO(SIGNO($A1:$C1);SIGNO($A1:$C1)) -----> in english: =SUMPRODUCT(SIGN($A1:$C1),SIGN($A1:$C1))
 
@Eben

Hi!

Discard my last suggestion, I misread your post: what I've wrote is for testing against equal 1 to validate the three values. I apologize.

Regards!


EDIT:

I tried your formula and it works with a couple of random values. Would you please consider uploading a sample file?
 
SirJB7 I will go read the three green topics.

Luke M What i need from the formula is firstly to find the cell (A1,B1 or C1)containing the bigger than 0 value. If value bigger than 0 is in A1 then copy G11 to E1, If value bigger than 0 is in B1 then copy H11 to E1, and if bigger than 0 is in C1 then copy I11 to E1.


Plse excuse my english, I am from Sout Africa and speak afrikaans.


Thanks
 
Hi, Eben!


Your formula placed in E1 cell should do the job. I've just tried it and it works fine.

Here's the file:

https://dl.dropbox.com/u/60558749/If%20formula%20-%20criteria%20in%20three%20cells%20%28for%20Eben%20at%20chandoo.org%29.xlsx


Give a look to column E for your formula. If you can get it working with your data, please consider uploading a sample file (guidelines within green topics).


Regards!


PS: don't worry about your English, here we all come from different countries... BA, Argentine, in my case, so I hardly even speak Spanish :)
 
SirJB7


Thanks for the file. I tried it on a blank sheet and it works 100%

In my sheet where i need it, it don't work but i will keep on trying.

I will post file if i do not get it right by tomorrow


Thank you very much again
 
You must log in or register to reply here.
Back
Top