Like the captioned, I would like to know how I could apply condition like "1<=U10<10" in Excel? I would like to set up a formula like this: =IF("1<=U10<10", "OK", "NOT OK")

What is this "1<=U10<10"??Similarly, how could I set up a formula so that if C10="N", D10="K", E10="P", then F10 will return the string "Completed"?

Thanks!

Try these and let us know if they fit your needs: =IF(AND(U10>=1,U10<10),"OK","Not OK")

=IF(AND(C10="N", D10="K", E10="P"),"Completed","")

That works!!

Thanks!!One follow-up question though:

Is it possible to use the "AND" together with "COUNTIF" function? I tried the following formula but it didn't work:=IF(K10="","",COUNTIF(AND((X10:X54, ">3"), (X10:X54, "<=4"))))

May I know what the problem is?

Thanks!

Hi, Three problems,

First, COUNTIF() only works on a single range, so you can't try and pass two ranges to it.

Secondly AND() is a logical function which returns either TRUE or FALSE

Thirdly if you look at COUNTIF() you have used it with only one argument, COUNTIF requires a range and a criteria.

I presume that you want to know how many cells in the range X10:X54 are >3 but <=4, i.e. between roughly 3.000000001 and 4.0

The function SUMPRODUCT() will do this. For each part of SUMPRODUCT it returns TRUE or 1 for each cell in the range that meets the criteria. If a cell does not meet one of the criteria it returns FALSE or 0 (zero).

It then multiplies each result for each cell together, so any cell that does not meet any one of the criteria returns zero (any value x zero = zero)

It then adds all the individual cell results together, i.e. in this case it adds a series of 1's and 0's for each cell=IF(K10="","",SUMPRODUCT((X10:X54>3) *(X10:X54<=4)))

This formula would return the sum of the values of the cells that meet the citeria:

=IF(K10="","",SUMPRODUCT((X10:X54>3) *(X10:X54<=4) *(X10:X54)))

as the final step is not a test, it is just the cell value, and for each cell its value is multiplied by the 1 or 0 from the two preceeding steps.Regards

Hi, Thanks for your explanations. I tried your formulas:

(1) =IF(K10="","",SUMPRODUCT((X10:X54>3) *(X10:X54<=4)))

(2) =IF(K10="","",SUMPRODUCT((X10:X54>3) *(X10:X54<=4) *(X10:X54)))Formula 1 works (returning the value "3"-as there are 3 cells, i.e. 3.4,3.6,3.8, falling within the range of 3<x<=4) while formula 2 doesn't work (returning the value "10.8"). Actually, what's the difference between the two formulas?

Besides, will there be any difference if the asterisks are replaced by commas?

Thanks again!!

Hi, Both formulas work 'as expected'

The first one returns the number of cells meeting the criteria

and the second one returns thetotal valueof all the cells meeting the criteria. (3.4 + 3.6 + 3.8 = 10.8) .If you replace * with , you will get a different result - try it and see if it works.

* is actually the multiplication symbol, as the result of each part of SUMPRODUCT() is multiplied together.

Regards

Ask Your Question

Weekly Poll

Do you think Google Fiber has a strong future?

Discuss in The Lounge

Poll History