Dynamic Sum function with filter

[Delimaster]

I'm trying to come up with a function that will let me search remaining values, filter them from small to largest, and then sum them based on a count reference in another cell.

I can get everything laid out, but i'm not sure how to only sum a range based on another cell value, and exclude blanks.

Any help?

 

[Benders]

Assuming your data outlined in Red is from M1:M50 and the Green Cell is in B4; Then the formula for Minimum Salary Spend spots could be:

For Lowest 10

=SUMIF(M1:M50,"<=" & SMALL(M1:M50,B4))

For Highest 10

=SUMIF(M1:M50,">=" & LARGE (M1:M50,B4))

Apologies in advance if I misunderstood your requirement.

 

[Delimaster]

This is working! Thank you! Is there anyway to only do the actual count? Since there are multiple values (in this case, 109 & 109) it is counting both of them in the top 10, instead of just the first "10")

 

[Benders]

If I was you I'd avoid doing that. Instead I would say in Column N, First generate a List of Distinct Values from column M and then apply the formula I shared above on Column N.
In case you don't know (apologies if you already did) how to create a list of distinct values then follow these steps:
1. Select the Entire range M1:M50 and give it a Name Reference say List.
2. Then Select only M1 and give it a named reference say List_Start
3. Then in Column N, starting from N2 enter the below formula and instead of Enter , hit CTRL + SHIFT + ENTER. This will create an array (you should be able to see Curly brackets {} at the start and end of the formula. Then drag this formula down
=IF(COUNT(IF(MATCH(List,List,0)=(ROW(List)-ROW(List_start)+1),INDEX(List,MATCH(List,List,0)),""))>=(ROW(List)-ROW(List_start)+1),INDEX(List,SMALL(IF(MATCH(List,List,0)=(ROW(List)-ROW(List_start)+1),MATCH(List,List,0),""),ROW(List)-ROW(List_start)+1)),"")
4. Once you have created this Distinct List of Entities modify the formula I shared earlier to get the result.

CAUTION: This could potentially slow down your worksheet. So don't drag the Distinct formula all the way to 50 rows. Keep it till the point you can see all the distinct values getting generated.

 

[Delimaster]

Hmm... it's not filling in any values when I do that

 

[Benders]

It is probably because of the spaces. We'll need to think of something else.

 

[Delimaster]

So, I have a moronic question.

If I have a range, and I want to sum all the values less than 130, but want to cap the sum amount at 120, how do I do that? I.e. if value equals between 120-130, than it would only sum 120 for that cell. If the cell has a value of 131, it would not sum. If the cell had a value of 119, it would sum 119.

EDIT: I figured it out... Ignore.

 

[Benders]

Use this formula in Column N to create a list of Distinct numbers from Column M. Just remember to

(a) drag it only as far as you need it. Stop when you see the last distinct value repeating itself.
(b) Be sure to enter this formula with CTRL+SHIFT+ENTER

=INDEX(List,MATCH(0,COUNTIF($A$1:A1,List),0))

(c) Once you have created this Distinct List of Entities modify the formula (=SUMIF(N1:N10,"<=" & SMALL(N1:N10,B4))) to get the result.

 

[Benders]

Ignore my last post. This too fails if there are spaces.

 

[Benders]

In case you are not averse to using VBA try this code.

Function CreateUnique(rng As Range) As Variant
    Dim row As Range
    Dim entity() As String
    Dim entitySize As Integer
    Dim newEntity As Boolean
    Dim i As Integer
    Dim Y As Integer
    Dim result As String

    entitySize = 0
    newEntity = True

    For Each row In rng.Rows
        If row.Value <> "" Then
            newEntity = True
            For i = 1 To entitySize Step 1
                If entity(i - 1) = row.Value Then
                    newEntity = False
                End If
            Next i
            If newEntity Then
                entitySize = entitySize + 1
                ReDim Preserve entity(entitySize - 1)
                entity(entitySize - 1) = row.Value
            End If
        End If
    Next

    Y = Range(Application.Caller.Address).row - rng.row

    If Y < entitySize Then
        result = entity(Y)
        CreateUnique = result
    Else
        CreateUnique = ""
    End If
End Function

Then in any column Just enter <code>=CreateUnique(range)</code> to a cell and drag. Like in N1 you can write CreateUnique($M$1:$M$50) and drag it down. Once you have created this Distinct List of Entities modify the formula (=SUMIF(N1:N10,"<=" & SMALL(N1:N10,B4))) to get the result.