## 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

Identify numbers in sum using solver in excel

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

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

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

Comments(13) Filed in category: Excel, Mmult, Permutations, Sum

Count unique distinct values within same week, month or year in excel

Introduction What is unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]Comments(8) Filed in category: Count values, Dates, Excel, Frequency, Sum, Unique distinct values, Year

### 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 […]