两个条件MINIF和MAXIF公式 [英] Two criteria MINIF and MAXIF formula

问题描述

我希望将以下excel表中最年轻的白猫的日期显示在一个单元格中.最小的会出生于2015年7月29日.

I want the Youngest White cat's date from the excel table below to be displayed in a cell. The youngest would be born 29/07/2015.

在另一个单元格中，我想显示最古老的白猫日期为18/07/2015.

In another cell i want to display the oldest white cat date 18/07/2015.

Column A ->Animal Column B ->Color Column C ->Date of birth

我知道公式是否适用于一个单元格

I know the formula if its is for one cell

 =IF(AND(A1="CAT",B1="White"),C1)


RowNo------A-----B--------C-----
 1------- CAT  White  20/07/2015
 2--------CAT  White  29/07/2015
 3--------CAT  White  18/07/2015
 4--------DOG  Black  29/07/2015
 5--------COW  White  29/07/2015
 6--------CAT  White  20/07/2015
 7--------COW  Black  29/07/2015
 8--------COW  Black  29/07/2015

推荐答案

您可以使用更新的

G2:H2中的公式为

The formulas in G2:H2 are,

=AGGREGATE(14, 6, ($A$2:$A$99=E2)*($B$2:$B$99=F2)*($C$2:$C$99), 1)
=AGGREGATE(15, 6, 1/($A$2:$A$99=E2)*($B$2:$B$99=F2)*($C$2:$C$99), 1)

那里有许多MINIF和MAXIF公式，但很少使用AGGREGATE.这是一个功能强大的new(er)函数，值得关注.

There are a host of MINIF and MAXIF formulas out there but few that use AGGREGATE. This is a powerful new(er) function and deserves some attention.

这是标准的非数组公式，不需要 Ctrl + Shift + Enter↵. AGGREGATE随Excel 2010引入.有关2010年前的解决方案，您可以参考

This is a standard non-array formula that does NOT require Ctrl+Shift+Enter↵. AGGREGATE was introduced with Excel 2010. For pre-2010 solutions you can refer to MINIF, MAXIF and MODEIF.

这篇关于两个条件MINIF和MAXIF公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！