☰

Excel array formulas to find smallest date with multiple criteria

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 microsoft-excel-2007 worksheet-function
Excel array formulas to find smallest date with multiple criteria

The array formula I have will give me the smallest date from a Service table, based on the following criteria:

  1. The value in the item column matches the item # in cell A1
  2. The value in the amount column > 0

=MIN(IF((IF(Service[Item]=$A1,TRUE)*IF(Service[Amount]>0,TRUE))>0,Service[Date],"z"))

Adding "z" was the only way I could get it to exclude 0/FALSE, which would always claim itself as the smallest value. Is there a cleaner way to calculate this? It will be part of a more complex formula.

答案1

Well, you should be able to use the formula without the inner IFs:

=MIN(IF((Service[Item]=$A1)*(Service[Amount]>0),Service[Date],9^99))

(Service[Item]=$A1) If this is true, it automatically returns true. Same for the next statement. And when you multiply both, you get a series of 0s and 1s which excel treats as false and true respectively.

And using I'm not sure if using z is working for you. It's returning me #VALUE! apparently...

相关内容