## Excel udf: Find numbers in sum

This article describes how to find a sum from a range of numbers using a user defined function.

Let´s see how quickly excel solver finds the numbers using the instructions from this post: Identify numbers in sum using solver in excel.

The sum is 1832,3 and it takes around 15 seconds to find the numbers.

With my beginner vba skills I created an user defined function to find numbers in a sum. See picture below.

The udf finds two sets of numbers in a second.

Udf in A23:Q25:

*CTRL + SHIFT + ENTER*

Formula in R23 sums the numbers..

Here is another example:

User defined function:

Function Find_num(rng As Range, cell As Range) Dim com() As Single, c As Single, i As Single Dim s As Single, d As Single, u As Single, v As Single Dim arr() As String, r As Single, p As Single, t As Single Dim sum_cells As Single, j As Single, k As Single, l As Single sum_cells = Application.WorksheetFunction.Sum(rng) c = rng.Rows.Count ReDim arr(1 To 1000, 1 To c) r = 1 For i = 0 To Int(c / 2) If i <> Int(c / 2) - 1 Then t = WorksheetFunction.Combin(c, i + 1) Else t = WorksheetFunction.Combin(c, i + 1) / 2 End If ReDim com(i) For j = 0 To i com(j) = j + 1 Next k = i For s = 1 To t If com(k) > c And i > 0 Then p = 0 Do Until com(k) <= c - p com(k - 1) = com(k - 1) + 1 k = k - 1 p = p + 1 Loop Do Until k >= i k = k + 1 com(k) = com(k - 1) + 1 Loop End If d = 0 For j = 0 To i d = d + rng(com(j)) Next j If d = cell Then For j = 1 To i + 1 arr(r, j) = rng(com(j - 1)) Next j r = r + 1 End If If sum_cells - d = cell Then For j = 1 To c v = 0 For u = 0 To i If rng(com(u)) = rng(j) Then v = 1 Next u If v = 0 Then arr(r, j) = rng(j) End If Next j r = r + 1 End If com(k) = com(k) + 1 Next s Next i Find_num = arr() End Function

**How to use user defined function in excel**

- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste the above user defined function
- Exit visual basic editor

### Download excel sample file for this tutorial.

Sum combinations.xls

(Excel 97-2003 Workbook *.xls)

### Category: Finance

Calculate your stock portfolio performance in excel

Track your stock investments in excel and use a web query to import current stock prices from yahoo. Setup excel […]Comments(31) Filed in category: Excel, Stock portfolio

Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel

Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]Comments(27) Filed in category: Excel, Stock portfolio

Excel udf: Import historical stock prices from yahoo – added features

This post describes how to import historical stock quotes from yahoo. This custom function is more advanced than the previous […]Comments(18) Filed in category: Excel, stock chart

Comments(14) Filed in category: Excel, Finance

Calculate your stock portfolio performance with Net Asset Value based on units in excel

In a previous related post we calculated the stock portfolio performance using the most current stock prices compared to buying […]Comments(13) Filed in category: Excel, Stock portfolio

### Category: Sum function

Identify numbers in sum using solver

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]Comments(48) Filed in category: Combinations, Excel, Solver, SUM function

Sum values between two dates with criteria in excel

In this post, I will provide a formula to sum values in column (Qty) where an column (Date) meets two […]Comments(27) Filed in category: Excel, SUM function

Sum values in a range where adjacent cell value equals a criterion in excel

Question: How do I sum all values in a range where adjacent cell value equals a criterion? The criterion is […]Comments(19) Filed in category: Excel, SUM function

Comments(13) Filed in category: Excel, MMULT function, Permutations, SUM function

Comments(7) Filed in category: Count values, Excel, MMULT function, SUM function

### 2 Responses to “Excel udf: Find numbers in sum”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[…] udf that I think could be converted into a macro to do what I want to do. The udf can be found at: Excel udf: Find numbers in sum | Get Digital Help - Microsoft Excel resource The code for the udf: Function Find_num(rng As Range, cell As Range) Dim com() As Single, c As […]

[…] target match, summatch.com, and they won't work with values like: 0.3157907543. I found a udf here: Excel udf: Find numbers in sum | Get Digital Help - Microsoft Excel resource but I need to convert it to a macro to work with the values I have like: 0.3157907543. There are 42 […]