## SUMIF across multiple sheets [UDF]

This post describes a User Defined Function that searches multiple ranges and adds corresponding values across worksheets in a workbook.

A User defined function is a custom function that anyone can build and use, you simply copy the code below and paste it to a code module in your workbook

This custom function works like the SUMIF function except that you can use multiple lookup and sum ranges.

Formula in cell C4:

### User Defined Function Syntax

SUMIFAMS(*lookup_value*, *lookup_range, sum_range*)

### Arguments

Parameter |
Text |

lookup_value |
Required. The value(s) you want to look for. |

lookup_range |
Required. The range you want to search. |

sum_range |
Required. The range you want to add. |

[lookup_range_2] |
Optional. You may have up to 127 additional argument pairs. |

[sum_range_2] |
Optional. |

### VBA code

'Name function and arguments Function SUMIFAMS(lookup_value As Range, ParamArray cellranges() As Variant) 'Declare variables and data types Dim i As Integer, rng1 As Variant, temp As Single, a As Boolean Dim rng2 As Variant, value As Variant, j As Single 'Make sure that the number of cell ranges are an even number. There must be a lookup_range and a sum_range. If (UBound(cellranges) + 1) Mod 2 <> 0 Then 'Display a message box. MsgBox "The number of range arguments must be even. 2, 4 , 8 ... and so on" 'End function so the user can correct the problem. Exit Function End If 'Iterate through all cell ranges For i = LBound(cellranges) To UBound(cellranges) Step 2 'Check that the number of rows in both cell ranges match If cellranges(i).Rows.Count <> cellranges(i + 1).Rows.Count Then MsgBox "The number of rows in range arguments don't match." End If 'Make sure that the ranges only contain one column If cellranges(i).Columns.Count <> 1 Then MsgBox "Range arguments can only have size one column each." Exit Function End If 'Save ranges to an array variable rng1 = cellranges(i).value rng2 = cellranges(i + 1).value 'Iterate through values in array variable For j = LBound(rng1) To UBound(rng1) 'Iterate through each lookup value if there are more than one For Each value In lookup_value 'Make a comparison (not case sensitive) If UCase(rng1(j, 1)) = UCase(value) Then a = True Next value 'Add corresponding number to temp variable if a= True If a = True Then temp = temp + rng2(j, 1) a = False Next j Next i 'Return total to worksheet SumifAMS = temp End Function

### Where to copy vba code?

- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste vba code.
- Exit visual basic editor

You can also use multiple search values, the returned number is a total based on both search values.

### Download Excel file

Enter your email to receive the workbook.Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

How to count word frequency in a cell range [UDF]

This user defined function creates a unique distinct list of words and how many times they occur in the selected […]

List files in a folder and subfolders [UDF]

This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]

Split words in a cell range into a cell each [UDF]

This post describes how to split words in a cell range into a cell each using a custom function. I […]

Split values equally into groups

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]

Filter unique distinct words from a cell range [UDF]

This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]

Count comma separated values [UDF]

I received an email from one of my seven blog readers. In Excel, I have a column, say A, with some […]

Lookup multiple values in one cell [UDF]

Chrisa asks: Hi Oscar...this is a very interesting function and helped me a lot so far. My file though is […]

The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, […]

Two-way lookup using multiple tables [UDF]

This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]

### One Response to “SUMIF across multiple sheets [UDF]”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

This was a very interesting technique. I've been trying to use something similar. Thanks for sharing.