How to apply condition like 1x10 in Excel

Microsoft Excel 2007
February 19, 2010 at 07:35:34
Specs: Windows XP
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"?


See More: How to apply condition like 1x10 in Excel

Report •

February 19, 2010 at 09:13:21
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","")

Report •

February 19, 2010 at 19:48:52
That works!!

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?

Report •

February 20, 2010 at 04:52:06

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.


Report •

Related Solutions

February 20, 2010 at 06:26:08
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!!

Report •

February 20, 2010 at 07:55:40

Both formulas work 'as expected'

The first one returns the number of cells meeting the criteria
and the second one returns the total value of 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.


Report •

Ask Question