I have a set of values to test Say 4 Cells. How would I return true only if a certain amount of them qualify as true. I want to do this in a function.
Ex: I test all 4 cells but I want it to be true only if 3 of the Cells return true.
or
I test all 4 cells but I want it to be true only if 1 of the Cells return true,
etc....
I know this would be possible some how but how? I tried to do a search on the web but cannot find a solution.
Hi
Looks to me that you need an IF(AND---OR...function. For Example.
=IF(AND(A1=B1;A2=B2;A3=B3);SUM(B1:B10);"")>>All 3 conditions for summing range B1:B10.
=IF(OR(A1=B1;A2=B2;A3=B3);SUM(B1:B10);"")>> 1 of 3 conditions for summing range B1:B10.
Hope to helps you.
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
Not sure what makes a cell "true".
This formula returns TRUE if only 3 of the cells in A1:D1 contain the word "Yes"
E1: =COUNTIF(A1:D1,"Yes")=3
Examples:
Yes Yes No Yes = TRUE
Yes Yes Yes Yes = FALSE
Does that hep?
Ron,
Thank you that looks like it would work. I had to modify it a little bit and here is what I got:
=COUNTIFS(E5,"<0",I5,"<0",M5,"<0",Q5,"<0",U5,"<0",Y5,"<0")
However, it is not coming out correct. I want it to count it if that cell is less than 0. After I figure that out than I will put in the if Statement to return true if value = 3 and so on.
That formula returns the count where ALL of those criteria are true.Originally Posted by djblois1
This regular formula returns the count of the referenced cells that are less than zero
Is that something you can work with?=SUM(COUNTIF(OFFSET(E5,,{0,4,8,12,16,20}),"<0"))
That works perfectly in a cell but Now I need to put it in conditional formation and it says that it will not work with Conditional formatting.
That's very different...It's important to give as much information as you can.
Conditional formatting doesn't allow any form of array or array formula.
Try this regular formula:
Is that something you can work with?=SUMPRODUCT(--(((MOD((COLUMN(E5:Y5)-1),4)=0)*E5:Y5)<0))=2
Sorry, I did not know there was a difference.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks