Convert SUMIFS Array to VBA

[xs4amit]

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

Month	Date	Emp_ID	Emp_Name	Attendance Status	Task1	Production for Task1 (PD1)	Task2	Production for Task2 (PD2)	Task3	Production for Task3 (PD3)
June	30/6/18	5673	John	Present	Survey_1	30	Survey_2	55	Survey_1	37
June	30/6/18	5675	Michele	Present	Survey_1	27	Survey_2	56
June	30/6/18	5734	Ethen	Present	Survey_3	56	Survey_1	32
July	01/7/18	5673	John	Present	Survey_3	42
July	01/7/18	5675	Michele	Present	Survey_1	53	Survey_3	23	Survey_1	113
July	01/7/18	5734	Ethen	Present	Survey_1	22

<tbody>
</tbody>

Sheet 2: Day-Wise Production stats

Month Filter	June	Task Filter	Survey_1
Emp_ID	30/6/18	01/7/18	02/7/18
5673	67
5675	27	166
5734	32	22

<tbody>
</tbody>

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

 

[kvsrinivasamurthy]

In Sheet2:
In A3, ARRAY formula. then drag down to A7

=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

=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.

 

[kvsrinivasamurthy]

To avoid 0 Formula for B3

=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))),"")

 

[tonyyy]

xs4amit,

You might consider the following...

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

 

[xs4amit]

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.

 

[tonyyy]

The Database sample did not include any matches for 2/7/2018.

 

[xs4amit]

xs4amit,

You might consider the following...

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

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.