Top 5 Sumif function?

B

bestrugger

I'm looking to add only the top 5 in a list of n records. Is that possible?
Something to the extent of

=sumifTOP(range, criteria, addrange,top#)

Doable? or something similar
 
D

daddylonglegs

Here's an example

=SUM(LARGE(IF(A1:A10="x",B1:B10),{1,2,3,4,5}))

sums the top 5 values in B1:B10 where A1:A10 ="x"

Note this will only sum 5 values even if there are ties

If you may have less than 5 values where the criteria is met then to sum the
top 5 or all if there are less than 5 then you could try

=IF(COUNTIF(A1:A10,"x"),SUM(LARGE(IF(A1:A10="x",B1:B10),ROW(INDIRECT("1:"&MIN(5,COUNTIF(A1:A10,"x")))))),0)

confirmed with CTRL+SHIFT+ENTER
 
Top