multiple criteria small function excel

Question

I am stuck in finding the least 10 values in excel using small function with multiple criteria and also excluding zeros.

• I have 4 columns, person, date, hours and weather.
• I want the least hours of a person considering all the dates and the weather should not be rainy.

I have used this formula:

=small(if(and(person="a",hours,"<>0",weather,"<>rainy"),hours),no)

where no represents the the 1-5 numbers.

Answer 1

There is one array formula solution provided already; another standard formula (i.e. non-CSE) method could be the newer AGGREGATE1 function which provides a layer of cyclic processing without having to finalize with CSE.

AGGREGATE has the option to ignore errors; by forcing any non-compliant combination of values into a #DIV/0! state, those values will be discarded from consideration. When all three conditions match, the denominator will be 1 and any number divided by 1 is unchanged.

=AGGREGATE(15, 6, C$2:C$13/((A$2:A$13=G$4)*(D$2:D$13=H$4)*(C$2:C$13<>0)), ROW(1:1))

Fill down for the second then third, etc. smallest. If you prefer to fill right, change ROW(1:1) to COLUMN(A:A) to attain a k increment laterally. Remember to 'anchor' the columns of the range and criteria references with $.

---

1 _{The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.}