VBA SumIfs return both 0s and correct results

alisoncleverly

New Member
Joined
Feb 20, 2020
Messages
28
Office Version
  1. 2013
Platform
  1. Windows
So I have a piece of codes using SumIfs to return Quarterly Totals, breakdown by Quarter and Yearly Total, breakdown by year. However, The Yearly Total, Q1 Total,Q1 breakdown, Q2 Total, Q2 breakdowns return correct results while Q3 and Q4 breakdowns all have 0s, even though Q3 and Q4 Totals return correct results.

Here is a snapshot of the template in which the code should return values.

1584637991016.png


As you can see from the print screen, Q2 results are correctly calculated and located (both breakdown numbers and total). However, for Q3, the total for each category is correct but the breakdowns are all 0s which is absurd since the formulas I used for each Quarter are exactly the same, only the ranges to calculate Quarterly values changed (which I double checked again is correct).

Here is my codes:
VBA Code:
Sub UpdateSnapshot()

'Set up Message Box
    If MsgBox("Update Snapshot?", vbYesNo + vbQuestion + vbDefaultButton2, "Opportunity Snapshot 2020") = vbNo Then
        Exit Sub
    End If

'Declare variables
    Dim wsOpps As Worksheet, wsSnapshot As Worksheet
    Dim r As Integer, c As Integer

    Set wsOpps = ThisWorkbook.Sheets("Opps tracker 2020-2021")
    Set wsSnapshot = ThisWorkbook.Sheets("Snapshot")

    Dim SumRgn As Range 'Total column in Opps worksheet
    Dim CrtYrPrime As Range
    Dim CrtCat As Range
    Dim CrtYrList As Range

    Dim CrtRgnPrime As Range
    Dim CrtRgnCat As Range
    Dim CrtRgnYr As Range

    Dim CrtRgnQ1 As Range 'Range Q1
    Dim CrtRgnQ2 As Range 'Range Q2
    Dim CrtRgnQ3 As Range 'Range Q3
    Dim CrtRgnQ4 As Range 'Range Q4

    Dim CrtQ1Prime As Range
    Dim CrtQ2Prime As Range
    Dim CrtQ3Prime As Range
    Dim CrtQ4Prime As Range

    With wsOpps
        Set SumRgn = .Range("T1:T2000") 'Total column in Opps
        Set CrtRgnPrime = .Range("C1:C2000") 'Prime Model
        Set CrtRgnCat = .Range("K1:K2000") 'Category
        Set CrtRgnYr = .Range("J1:J2000") 'Year

        Set CrtRgnQ1 = .Range("L1:L2000") 'Quarter 1
        Set CrtRgnQ2 = .Range("N1:N2000") 'Quarter 2
        Set CrtRgnQ3 = .Range("P1:P2000") 'Quarter 3
        Set CrtRgnQ4 = .Range("R1:R2000") 'Quarter 4
    End With

    With wsSnapshot
        Set CrtYrPrime = .Range("$A$3") 'Prime Model for Year x
        Set CrtQ1Prime = .Range("$A$22")
        Set CrtQ2Prime = .Range("$A$41")
        Set CrtQ3Prime = .Range("$A$60")
        Set CrtQ4Prime = .Range("$A$79")

        Set CrtCat = .Range("$B$1") 'Category
        Set CrtYrList = .Range("$A$1") 'Year list

    End With

'IMPORTANT -- Turn off events
    Application.EnableEvents = False

'Clear old data in Worksheet Snapshot
    wsSnapshot.Range("B3:K20, B22:K39, B41:K58, B60:K77, B79:K96").ClearContents

'Yearly breakdown
    For r = 3 To 19
        For c = 2 To 11
            wsSnapshot.Cells(r, c) _
                = Application.WorksheetFunction.SumIfs(SumRgn, CrtRgnPrime, CrtYrPrime.Offset(r - 3, 0), _
                    CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
        Next c
    Next r
'Yearly Total
    For r = 20 To 20
        For c = 2 To 11
            wsSnapshot.Cells(r, c) _
                = Application.WorksheetFunction.SumIfs(SumRgn, CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
        Next c
    Next r

'Q1 breakdown
    For r = 22 To 38
        For c = 2 To 11
            wsSnapshot.Cells(r, c) _
                = Application.WorksheetFunction.SumIfs(CrtRgnQ1, CrtRgnPrime, CrtQ1Prime.Offset(r - 3, 0), _
                    CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
        Next c
    Next r
'Q2 breakdown
    For r = 41 To 57
        For c = 2 To 11
            wsSnapshot.Cells(r, c) _
                = Application.WorksheetFunction.SumIfs(CrtRgnQ2, CrtRgnPrime, CrtQ2Prime.Offset(r - 3, 0), _
                    CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
        Next c
    Next r
'Q3 breakdown
    For r = 60 To 76
        For c = 2 To 11
            wsSnapshot.Cells(r, c) _
                = Application.WorksheetFunction.SumIfs(CrtRgnQ3, CrtRgnPrime, CrtQ3Prime.Offset(r - 3, 0), _
                    CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
        Next c
    Next r
'Q4 breakdown
    For r = 79 To 95
        For c = 2 To 11
            wsSnapshot.Cells(r, c) _
                = Application.WorksheetFunction.SumIfs(CrtRgnQ4, CrtRgnPrime, CrtQ4Prime.Offset(r - 3, 0), _
                    CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
        Next c
    Next r

'Q1 Total
    For r = 39 To 39
        For c = 2 To 11
            wsSnapshot.Cells(r, c) _
                = Application.WorksheetFunction.SumIfs(CrtRgnQ1, CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
        Next c
    Next r
'Q2 Total
    For r = 58 To 58
        For c = 2 To 11
            wsSnapshot.Cells(r, c) _
                = Application.WorksheetFunction.SumIfs(CrtRgnQ2, CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
        Next c
    Next r
'Q3 Total
    For r = 77 To 77
        For c = 2 To 11
            wsSnapshot.Cells(r, c) _
                = Application.WorksheetFunction.SumIfs(CrtRgnQ3, CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
        Next c
    Next r
'Q4 Total
    For r = 96 To 96
        For c = 2 To 11
            wsSnapshot.Cells(r, c) _
                = Application.WorksheetFunction.SumIfs(CrtRgnQ4, CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
        Next c
    Next r

'IMPORTANT -- Turn on events
    Application.EnableEvents = True

End Sub

I don't know why but Q1, Q2 share the exact same formulas with Q3 and Q4 but Q3, Q4 somehow don't work.

This is on my "Snapshot" worksheet:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    Call UpdateSnapshot
End If
End Sub

Please advise! Any help is highly appreciated! Thank you!
 
Last edited:

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
The problem is the beginning of the row.

Rich (BB code):
'Q1 breakdown
    For r = 22 To 38
      For c = 2 To 11
        wsSnapshot.Cells(r, c) _
          = Application.WorksheetFunction.SumIfs(CrtRgnQ1, CrtRgnPrime, CrtQ1Prime.Offset(r - 22, 0), _
            CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
      Next c
    Next r
'Q2 breakdown
    For r = 41 To 57
      For c = 2 To 11
        wsSnapshot.Cells(r, c) _
          = Application.WorksheetFunction.SumIfs(CrtRgnQ2, CrtRgnPrime, CrtQ2Prime.Offset(r - 41, 0), _
            CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
      Next c
    Next r
'Q3 breakdown
    For r = 60 To 76
      For c = 2 To 11
        wsSnapshot.Cells(r, c) _
          = Application.WorksheetFunction.SumIfs(CrtRgnQ3, CrtRgnPrime, CrtQ3Prime.Offset(r - 60, 0), _
            CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
      Next c
    Next r
'Q4 breakdown
    For r = 79 To 95
      For c = 2 To 11
        wsSnapshot.Cells(r, c) _
          = Application.WorksheetFunction.SumIfs(CrtRgnQ4, CrtRgnPrime, CrtQ4Prime.Offset(r - 79, 0), _
            CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
      Next c
    Next r

_______________________________________________________________________________________________________
You can simplify the use of for with this:

VBA Code:
'Q1 breakdown
  With wsSnapshot.Range("B22:K38")
    .Formula = "=SUMIFS(" & CrtRgnQ1.Address(external:=True) & "," & CrtRgnPrime.Address(external:=True) & ",$A22," & _
        CrtRgnCat.Address(external:=True) & ",B$1," & CrtRgnYr.Address(external:=True) & ",$A$1)"
    .Value = .Value
  End With
'Q2 breakdown
  With wsSnapshot.Range("B41:K57")
    .Formula = "=SUMIFS(" & CrtRgnQ2.Address(external:=True) & "," & CrtRgnPrime.Address(external:=True) & ",$A41," & _
        CrtRgnCat.Address(external:=True) & ",B$1," & CrtRgnYr.Address(external:=True) & ",$A$1)"
    .Value = .Value
  End With
'Q3 breakdown
  With wsSnapshot.Range("B60:K76")
    .Formula = "=SUMIFS(" & CrtRgnQ3.Address(external:=True) & "," & CrtRgnPrime.Address(external:=True) & ",$A60," & _
        CrtRgnCat.Address(external:=True) & ",B$1," & CrtRgnYr.Address(external:=True) & ",$A$1)"
    .Value = .Value
  End With
'Q4 breakdown
  With wsSnapshot.Range("B79:K95")
    .Formula = "=SUMIFS(" & CrtRgnQ4.Address(external:=True) & "," & CrtRgnPrime.Address(external:=True) & ",$A79," & _
        CrtRgnCat.Address(external:=True) & ",B$1," & CrtRgnYr.Address(external:=True) & ",$A$1)"
    .Value = .Value
  End With
____________________________________________________________________________________________________________________
Or better yet, simplify the 4 quarters like this:

VBA Code:
  Dim aRngs As Variant, aCrts As Variant, i As Long, rCrt As Range
  aRngs = Array("B22:K38", "B41:K57", "B60:K76", "B79:K95")
  aCrts = Array("L1:L2000", "N1:N2000", "P1:P2000", "R1:R2000")
    
  For i = 0 To UBound(aRngs)
    With wsSnapshot.Range(aRngs(i))
      Set rCrt = wsOpps.Range(aCrts(i))
      .Formula = "=SUMIFS(" & rCrt.Address(external:=True) & "," & _
        CrtRgnPrime.Address(external:=True) & ",$A" & Mid(aRngs(i), 2, 2) & "," & _
        CrtRgnCat.Address(external:=True) & ",B$1," & CrtRgnYr.Address(external:=True) & ",$A$1)"
      .Value = .Value
    End With
  Next
 

alisoncleverly

New Member
Joined
Feb 20, 2020
Messages
28
Office Version
  1. 2013
Platform
  1. Windows
The problem is the beginning of the row.

Rich (BB code):
'Q1 breakdown
    For r = 22 To 38
      For c = 2 To 11
        wsSnapshot.Cells(r, c) _
          = Application.WorksheetFunction.SumIfs(CrtRgnQ1, CrtRgnPrime, CrtQ1Prime.Offset(r - 22, 0), _
            CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
      Next c
    Next r
'Q2 breakdown
    For r = 41 To 57
      For c = 2 To 11
        wsSnapshot.Cells(r, c) _
          = Application.WorksheetFunction.SumIfs(CrtRgnQ2, CrtRgnPrime, CrtQ2Prime.Offset(r - 41, 0), _
            CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
      Next c
    Next r
'Q3 breakdown
    For r = 60 To 76
      For c = 2 To 11
        wsSnapshot.Cells(r, c) _
          = Application.WorksheetFunction.SumIfs(CrtRgnQ3, CrtRgnPrime, CrtQ3Prime.Offset(r - 60, 0), _
            CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
      Next c
    Next r
'Q4 breakdown
    For r = 79 To 95
      For c = 2 To 11
        wsSnapshot.Cells(r, c) _
          = Application.WorksheetFunction.SumIfs(CrtRgnQ4, CrtRgnPrime, CrtQ4Prime.Offset(r - 79, 0), _
            CrtRgnCat, CrtCat.Offset(0, c - 2), CrtRgnYr, CrtYrList)
      Next c
    Next r

_______________________________________________________________________________________________________
You can simplify the use of for with this:

VBA Code:
'Q1 breakdown
  With wsSnapshot.Range("B22:K38")
    .Formula = "=SUMIFS(" & CrtRgnQ1.Address(external:=True) & "," & CrtRgnPrime.Address(external:=True) & ",$A22," & _
        CrtRgnCat.Address(external:=True) & ",B$1," & CrtRgnYr.Address(external:=True) & ",$A$1)"
    .Value = .Value
  End With
'Q2 breakdown
  With wsSnapshot.Range("B41:K57")
    .Formula = "=SUMIFS(" & CrtRgnQ2.Address(external:=True) & "," & CrtRgnPrime.Address(external:=True) & ",$A41," & _
        CrtRgnCat.Address(external:=True) & ",B$1," & CrtRgnYr.Address(external:=True) & ",$A$1)"
    .Value = .Value
  End With
'Q3 breakdown
  With wsSnapshot.Range("B60:K76")
    .Formula = "=SUMIFS(" & CrtRgnQ3.Address(external:=True) & "," & CrtRgnPrime.Address(external:=True) & ",$A60," & _
        CrtRgnCat.Address(external:=True) & ",B$1," & CrtRgnYr.Address(external:=True) & ",$A$1)"
    .Value = .Value
  End With
'Q4 breakdown
  With wsSnapshot.Range("B79:K95")
    .Formula = "=SUMIFS(" & CrtRgnQ4.Address(external:=True) & "," & CrtRgnPrime.Address(external:=True) & ",$A79," & _
        CrtRgnCat.Address(external:=True) & ",B$1," & CrtRgnYr.Address(external:=True) & ",$A$1)"
    .Value = .Value
  End With
____________________________________________________________________________________________________________________
Or better yet, simplify the 4 quarters like this:

VBA Code:
  Dim aRngs As Variant, aCrts As Variant, i As Long, rCrt As Range
  aRngs = Array("B22:K38", "B41:K57", "B60:K76", "B79:K95")
  aCrts = Array("L1:L2000", "N1:N2000", "P1:P2000", "R1:R2000")
   
  For i = 0 To UBound(aRngs)
    With wsSnapshot.Range(aRngs(i))
      Set rCrt = wsOpps.Range(aCrts(i))
      .Formula = "=SUMIFS(" & rCrt.Address(external:=True) & "," & _
        CrtRgnPrime.Address(external:=True) & ",$A" & Mid(aRngs(i), 2, 2) & "," & _
        CrtRgnCat.Address(external:=True) & ",B$1," & CrtRgnYr.Address(external:=True) & ",$A$1)"
      .Value = .Value
    End With
  Next

Dante, thank you for the nth time for helping me out! they work perfectly! especially the shorter codes, amazing!!! Thanks again!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,538
Messages
5,625,397
Members
416,100
Latest member
lirongr1996

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
Top