I have a very simple SUMIF that looks something like this

```lngth = Cells(Rows.Count, 9).End(xlUp).Row

For i = 2 To lngth

Range("L" & i & ":L" & lngth) = Application.WorksheetFunction.SumIf(Range("Sheet2!A\$2:A\$1000"), Cells(i, 9), Range("H\$2:H\$1000"))
Next i
```

Sadly, I no longer have access to the data range. But now, the data is shown in a variety of ways. How would I modify the SUMIF so that an array was used in place of the ranges?

Feb 7, 2023 in Others 736 views

## 1 answer to this question.

You can't, in my opinion. When you pass an array into Sumif when it is expecting a range, it returns a type mismatch. You can try using this Sumif substitute that I made.

Testing requires the range A1:B6 to look like this.

A B
a 1
a 1
b 1
c 1
d 1
a 1

Code:

```Sub test()
Dim arr() As Variant
arr = Range("A1:B6")
Debug.Print mySumif(arr, "a", 1, 2)
End Sub

Function mySumif(ByVal arr As Variant, ByVal criteria As Variant, ByVal criteriaColNo As Integer, ByVal sumColNo As Integer) As Double
For i = LBound(arr) To UBound(arr)
If arr(i, criteriaColNo) = criteria Then mySumif = mySumif + arr(i, sumColNo)
Next i
End Function
```

Output:

`3`
answered Feb 7, 2023 by
• 63,720 points

## How do I determine the size of my array in C?

How do I determine the size of ...READ MORE

## How do I get an Excel range using row and column numbers in VSTO / C#?

Use: int countRows = xlWorkSheetData.UsedRange.Rows.Count; int countColumns = xlWorkSheetData.UsedRange.Columns.Count; object[,] ...READ MORE

## How can I count the rows with data in an Excel sheet?

With formulas, what you can do is: in ...READ MORE

## How can I replace the nth occurence using regex in VBA?

Use: =RegexReplace("This 9 is 8 a 77 6 ...READ MORE

## Copy the respective values from another workbook depend on specific conditions

Try this: Sub Get_Respective_Values_Of_Last_Closing_Date() Dim wb1 ...READ MORE

## Excel VBA compare values on multiple rows and execute additional code

I would use a Dictionary & Collection ...READ MORE

## How to concatenate elements of a single-dimensional array using VBA?

Using Microsoft 365's UNIQUE and SORT in VBA ' This is a ...READ MORE

## Loops through Check Box in VBA

Please attempt the next option. Assuming you ...READ MORE