Convert SUMIFS Array to VBA

X

xs4amit

New Member
Joined
May 21, 2018
Messages
34
Hi,

Thanks for showing interest in this post.

I have 2 excel sheets one of which has entire database and 2nd sheet contains day-wise production data as below

Sheet 1: Database

[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Date[/TD]
[TD]Emp_ID[/TD]
[TD]Emp_Name[/TD]
[TD]Attendance Status[/TD]
[TD]Task1[/TD]
[TD]Production for Task1 (PD1)[/TD]
[TD]Task2[/TD]
[TD]Production for Task2 (PD2)[/TD]
[TD]Task3[/TD]
[TD]Production for Task3 (PD3)[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]30/6/18[/TD]
[TD]5673[/TD]
[TD]John[/TD]
[TD]Present[/TD]
[TD]Survey_1[/TD]
[TD]30[/TD]
[TD]Survey_2[/TD]
[TD]55[/TD]
[TD]Survey_1[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]30/6/18[/TD]
[TD]5675[/TD]
[TD]Michele[/TD]
[TD]Present[/TD]
[TD]Survey_1[/TD]
[TD]27[/TD]
[TD]Survey_2[/TD]
[TD]56[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]30/6/18[/TD]
[TD]5734[/TD]
[TD]Ethen[/TD]
[TD]Present[/TD]
[TD]Survey_3[/TD]
[TD]56[/TD]
[TD]Survey_1[/TD]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD]01/7/18[/TD]
[TD]5673[/TD]
[TD]John[/TD]
[TD]Present[/TD]
[TD]Survey_3[/TD]
[TD]42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD]01/7/18[/TD]
[TD]5675[/TD]
[TD]Michele[/TD]
[TD]Present[/TD]
[TD]Survey_1[/TD]
[TD]53[/TD]
[TD]Survey_3[/TD]
[TD]23[/TD]
[TD]Survey_1[/TD]
[TD]113[/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD]01/7/18[/TD]
[TD]5734[/TD]
[TD]Ethen[/TD]
[TD]Present[/TD]
[TD]Survey_1[/TD]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2: Day-Wise Production stats

[TABLE="width: 500"]
<tbody>[TR]
[TD]Month Filter[/TD]
[TD]June[/TD]
[TD]Task Filter[/TD]
[TD]Survey_1[/TD]
[/TR]
[TR]
[TD]Emp_ID[/TD]
[TD]30/6/18[/TD]
[TD]01/7/18[/TD]
[TD]02/7/18[/TD]
[/TR]
[TR]
[TD]5673[/TD]
[TD]67[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5675[/TD]
[TD]27[/TD]
[TD]166[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5734[/TD]
[TD]32[/TD]
[TD]22[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
For this i am using 2 formulas (1st- to get a unique list of Emp_IDs who worked in the given task on a given day with this formula:

{=IFERROR(INDEX(Database[Emp_ID],
MATCH(0,
IF(Task Filter=Database[Task1],IF(Month Filter=Database[Month],COUNTIF($A$2:A2,Database[Emp_ID])),
IF(Task Filter=Database[Task2],IF(Month Filter=Database[Month],COUNTIF($A$2:A2,Database[Emp_ID])),
IF(Task Filter=Database[Task3],IF(Month Filter=Database[Month],COUNTIF($A$2:A2,Database[Emp_ID])),
""))),0)),"")}

And to calculate Production for the unique list for a particular task, i am using this below SUMIFS Array formula

{=IF(
(SUMIFS(Database[PD1],Database[Emp_ID],A3:A5,Database[Date],B2:B3,Database[Task1],$D$1)+
SUMIFS(Database[PD2],Database[Emp_ID],A3:A5,Database[Date],B2:B3,Database[Task2],$D$1)+
SUMIFS(Database[PD3],Database[Emp_ID],A3:A5,Database[Date],B2:B3,Database[Task3],$D$1)}


Now the challenge is that the database is huge (Almost 30000 Rows and 7 type of tasks) and these formulas are making the sheet too slow.

Please suggest if there is any fast way to do it. Or any VBA code. Actually i am new to VBA world. Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
In Sheet2:
In A3, ARRAY formula. then drag down to A7
Code: 
=IFERROR(INDEX(Database[Emp_ID],SMALL(IF(COUNTIF($A$2:$A2,Database[Emp_ID])=0,ROW(Database[Emp_ID]),""),1)-ROW($A$2)+1),"")
In B3, ARRAY formula. then drag across till D7
Code: 
=SUM((Database[Date]=B$2)*(Database[Emp_ID]=$A3)*IFERROR((Database[[Task1]:[Task3]]=$D$1)*(Database[[Production for Task1 (PD1)]:[Production for Task3 (PD3)]]),0))

How ARRAY formula is entered

Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.
Then drag as required using Fill Handle.
 
Last edited:
Upvote 0
To avoid 0 Formula for B3
Code: 
=IFERROR(1/(1/SUM((Database[Date]=B$2)*(Database[Emp_ID]=$A3)*IFERROR((Database[[Task1]:[Task3]]=$D$1)*(Database[[Production for Task1 (PD1)]:[Production for Task3 (PD3)]]),0))),"")
 
Upvote 0
xs4amit,

You might consider the following...

Code: 
Sub DayWiseProduction_1064703()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim tFilter As String
Dim arr1 As Variant
Dim r As Long, c As Long, lastRow As Long, nRow As Long, summ As Long, i As Long
Dim d8 As Range

Set ws = Sheets("Day-Wise Production stats")
tFilter = ws.Range("D1")
arr1 = Sheets("Database").UsedRange.Value
summ = 0
With ws
    .UsedRange.Offset(2, 0).Clear
    For r = 2 To UBound(arr1)
        For c = 6 To UBound(arr1, 2)
            If arr1(r, c) = tFilter Then
                Set d8 = .Rows(2).Find(What:=arr1(r, 2), After:=.Cells(2, 1), LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
                If Not d8 Is Nothing Then summ = summ + arr1(r, c + 1)
            End If
        Next c
        If Not d8 Is Nothing Then
            lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
            For i = 2 To lastRow
                If lastRow = 2 Then
                    nRow = 3
                    Exit For
                ElseIf .Cells(i, 1) = arr1(r, 3) Then
                    nRow = i
                    Exit For
                Else
                    nRow = lastRow + 1
                End If
            Next i
            .Cells(nRow, 1).Value = arr1(r, 3)
            .Cells(nRow, d8.Column).Value = summ
            Set d8 = Nothing
            summ = 0
        End If
    Next r
    .Activate
End With
End Sub

On the Day-Wise Production stats sheet, the Month filter seems to return both June and July dates - a bit confusing to me; the code just references the dates in Row 2 and ignores the Month filter.

Cheers,

tonyyy
 
Upvote 0
HI Tonyyy,

I apologies for late reply as i was away from accessing internet for last couple of days.

And thanks a lot. Your code is working like Magic. I tested it today & I modified it a little as per my need as my actual database has a little different structure but idea is the same.
Thanks you again.

If you can extend a little bit more help, can you please make the same formula to populate results from 2nd ROW and 4th Column ie cell(2,4).
That is where i am feeling a little challenge.
 
Last edited:
Upvote 0
The Database sample did not include any matches for 2/7/2018.
 
Upvote 0
tonyyy said:
xs4amit,

You might consider the following...

Code: 
Sub DayWiseProduction_1064703()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim tFilter As String
Dim arr1 As Variant
Dim r As Long, c As Long, lastRow As Long, nRow As Long, summ As Long, i As Long
Dim d8 As Range

Set ws = Sheets("Day-Wise Production stats")
tFilter = ws.Range("D1")
arr1 = Sheets("Database").UsedRange.Value
summ = 0
With ws
    .UsedRange.Offset(2, 0).Clear
    For r = 2 To UBound(arr1)
        For c = 6 To UBound(arr1, 2)
            If arr1(r, c) = tFilter Then
                Set d8 = .Rows(2).Find(What:=arr1(r, 2), After:=.Cells(2, 1), LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
                If Not d8 Is Nothing Then summ = summ + arr1(r, c + 1)
            End If
        Next c
        If Not d8 Is Nothing Then
            lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
            For i = 2 To lastRow
                If lastRow = 2 Then
                    nRow = 3
                    Exit For
                ElseIf .Cells(i, 1) = arr1(r, 3) Then
                    nRow = i
                    Exit For
                Else
                    nRow = lastRow + 1
                End If
            Next i
            .Cells(nRow, 1).Value = arr1(r, 3)
            .Cells(nRow, d8.Column).Value = summ
            Set d8 = Nothing
            summ = 0
        End If
    Next r
    .Activate
End With
End Sub

On the Day-Wise Production stats sheet, the Month filter seems to return both June and July dates - a bit confusing to me; the code just references the dates in Row 2 and ignores the Month filter.

Cheers,

tonyyy
Click to expand...



Hi Tonny,

Can you please help me a little bit more with this Code.

As your code is directly placing the values with ".Cells(nRow, d8.Column).Value = summ" in the sheet. Can we store these values in a multidimensional array and perform calculations on it.
For example:

I wants to do "SUM" of the production of an employee for the entire Month (or calculated dates) in last column?

Thanks and appreciate your help in advance.
 
Upvote 0
You must log in or register to reply here.

Similar threads

staticfluids
  • Question Question
Distribute Number into Several Part (Increment) Based on Other Data (Dynamic Array)
Replies
2
Views
397
staticfluids
staticfluids
L
  • Solved
SUMIFS based on multiple criteria and between date
2
Replies
17
Views
947
etaf
etaf
Rob_010101
  • Question Question
Filter Data?
2
Replies
16
Views
1K
kevin9999
K
K
  • Question Question
Modifying Existing VBA Code to add 2 additional data columns
Replies
0
Views
330
keranali
K
Lacan
  • Question Question
How to filter function and SUM based on Partial Text Match (SUMIFS with wildcards)
2
Replies
17
Views
1K
Lacan
Lacan

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top