VBA How do I replace the range with an array in SUMIF

0 votes

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 by Kithuzzz
• 38,000 points
903 views

1 answer to this question.

0 votes

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 narikkadan
• 63,600 points

Related Questions In Others

0 votes
0 answers

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

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

May 1, 2022 in Others by Kichu
• 19,040 points
497 views
0 votes
1 answer

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

answered Nov 17, 2022 in Others by narikkadan
• 63,600 points
1,802 views
0 votes
1 answer

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

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

answered Dec 27, 2022 in Others by narikkadan
• 63,600 points
636 views
0 votes
1 answer

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

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

answered Jan 13, 2023 in Others by narikkadan
• 63,600 points
1,210 views
0 votes
1 answer

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

answered Feb 6, 2023 in Others by narikkadan
• 63,600 points
636 views
0 votes
1 answer

Excel VBA compare values on multiple rows and execute additional code

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

answered Feb 7, 2023 in Others by narikkadan
• 63,600 points
1,913 views
0 votes
1 answer

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

answered Feb 16, 2023 in Others by narikkadan
• 63,600 points
719 views
0 votes
1 answer

Loops through Check Box in VBA

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

answered Apr 1, 2023 in Others by narikkadan
• 63,600 points
992 views
0 votes
1 answer

How do I protect all worksheet in an Excel workbook with a single click?

VBA Code : Dim ws as Worksheet Dim pwd ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 63,600 points
887 views
0 votes
1 answer

How do I combine the first character of a cell with another cell in Excel?

Try this: =CONCATENATE(LEFT(A1,1), B1) READ MORE

answered Nov 7, 2022 in Others by narikkadan
• 63,600 points
1,505 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP