前5个符合条件的单元格的SUMIF [英] SUMIF for first 5 cells meeting criteria
问题描述
简单的Excel表,例如
Simple Excel Table such as
A B
1 John 5
2 John 7
3 John 9
4 Jill 25
5 John 21
6 John 22
7 Jill 50
8 John 100
9 John 2000
10 Jack 4
使用SUMIF,我们可以返回分配给John的总数.
Using SUMIF, we can return the total assigned to John.
=SUMIF(A:A,"John",B:B)
有没有办法只返回与条件匹配的前5个值?还是有办法为John返回5个最小值?任何一种都可以.
Is there a way to return only the first 5 values that match the criteria? Or is there a way to return the 5 smallest values for John? Either would work.
推荐答案
哦,很好.我将继续假定您具有Excel 2010或更高版本.
Oh well. I'll go ahead and presume that you have Excel 2010 or later.
例如在D1中输入"John",在E1中输入以下公式:
With e.g. "John" in D1, enter this formula in E1:
= SUMIFS($ B $ 1:$ B $ 10,$ A $ 1:$ A $ 10,D1,$ B $ 1:$ B $ 10,< ="& AGGREGATE(15,6,$ B $ 1:$B $ 10/(($ A $ 1:$ A $ 10 = D1),5))
=SUMIFS($B$1:$B$10,$A$1:$A$10,D1,$B$1:$B$10,"<="&AGGREGATE(15,6,$B$1:$B$10/($A$1:$A$10=D1),5))
向下复制以得到与D2,D3等中的名称相似的结果.
Copy down to give similar results for names in D2, D3, etc.
致谢
这篇关于前5个符合条件的单元格的SUMIF的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!