Results 1 to 10 of 10

Thread: Sumifs Formula

  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Sumifs Formula

    I have a spreadheet with values in Column G and text in columns G.

    I want to do the following:

    1) Add all the Debits in Column G, excluding debit values where text "SARS" appears in Column F i.e excluding Debits pertaining to SARS
    2) Add all the Credits in Column G, excluding credit values where text "SARS" appears in Column F i.e exccluding credits pertaining to SARS

    I have used the sum formula to show you want I want to achieve. Possibly using the SUMIFS will solve the problem, but i'm not sure how to tackle this

    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Goulburn. NSW, Australia
    Posts
    20
    Thanks
    3
    Thanked 4 Times in 4 Posts

    Sum values depending on 2 conditions

    I think the following formulas will do what you want.

    =SUMIFS(G2:G12,G2:G12,">0",F2:F12,"<>SARS")
    =SUMIFS(G2:G12,G2:G12,"<0",F2:F12,"<>SARS")

    Just replace the 2s and 12s with the appropriate row numbers.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    SUMIFS Formula

    Thanks for the help, much appreciated

    I have amended the formula as follows:

    =SUMIFS(G1:G25,G1:G25,">0",F1:F25,"<>SARS*")

    How to I set this formula as VBA Code to usesumifs from the First row to the last row containing data? It would be appreciated if you could assist

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Here is a non-VBA version of the formula
    =SUMIFS(OFFSET(G1,0,0,COUNT($A:$A),1),OFFSET(G1,0, 0,COUNT($A:$A),1),">0",OFFSET(F1,0,0,COUNT($A:$A), 1),"<>SARS")
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Goulburn. NSW, Australia
    Posts
    20
    Thanks
    3
    Thanked 4 Times in 4 Posts
    If you still need the VBA, I think this will do the trick.

    Code:
    ' Create SUMIFS formulas for Non-SARS credits & debits.
    Sub CreateSumifs()
        Const clngSumCol As Long = 7    ' Column G
        Const csFormula = "=SUMIFS(#1:#@,#1:#@,"">0"",%1:%@,""<>SARS*"")"
        Dim lngLastRow As Long
        Dim sLastRow As String
        Dim sCredit As String
        Dim sDebit As String
        ' Find the current last row in the data.
        lngLastRow = ActiveSheet.Cells(1, clngSumCol).CurrentRegion.Rows.Count
        sLastRow = Format(lngLastRow)
        ' Create the credit formula.
        sCredit = Replace(csFormula, "@", sLastRow)
        sCredit = Replace(sCredit, "#", ColNoToColStr(clngSumCol))
        sCredit = Replace(sCredit, "%", ColNoToColStr(clngSumCol - 1))
        ' Create the debit formula.
        sDebit = Replace(sCredit, ">0", "<0")
        ' Write the formulas to the sheet.
        ActiveSheet.Cells(lngLastRow + 1, clngSumCol).Formula = sCredit
        ActiveSheet.Cells(lngLastRow + 2, clngSumCol).Formula = sDebit
    End Sub
    ' Convert a column number to a column string, eg: 1 -> A, 2 -> B
    Function ColNoToColStr(lngCol As Long) As String
        Dim vVal As Variant
        vVal = Split(Worksheets(1).Columns(lngCol).Address, "$")
        ColNoToColStr = Replace(vVal(1), ":", vbNullString)
    End Function

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    SUMIFS Formula

    Thanks for the help, much appreciated

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, you could shorten that by using R1C1 referencing:
    Code:
    Sub CreateSumifs()    Const clngSumCol As Long = 7    ' Column G
        Dim lngLastRow As Long
        ' Find the current last row in the data.
        lngLastRow = ActiveSheet.Cells(Rows.Count, clngSumCol).End(xlUp).Row
        ActiveSheet.Cells(lngLastRow + 1, clngSumCol).FormulaR1C1 = "=SUMIFS(R1C:R[-1]C,R1C:R[-1]C,"">0"",R1C[-1]:R[-1]C[-1],""<>SARS"")"
        ActiveSheet.Cells(lngLastRow + 2, clngSumCol).FormulaR1C1 = "=SUMIFS(R1C:R[-2]C,R1C:R[-2]C,""<0"",R1C[-1]:R[-2]C[-1],""<>SARS"")"
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  8. The Following User Says Thank You to rory For This Useful Post:

    CephasOz (2012-01-20)

  9. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    Goulburn. NSW, Australia
    Posts
    20
    Thanks
    3
    Thanked 4 Times in 4 Posts
    rory, thanks for your concise code. I've used Excel since it first came out, including R1C1 notation, and I've never seen an absolute reference in R1C1 before today. They've all been relative references in brackets. Even the trick of omitting the number for a reference in the same column had escaped me - I'd always used "C[0]".

    Could I ask you to explain why you prefer:
    lngLastRow = ActiveSheet.Cells(Rows.Count,clngSumCol).End(xlUp) .Row

    In addition to:
    ' Last row in contiguous area.
    lngLastRow = ActiveSheet.Cells(1,clngSumCol).CurrentRegion.Rows .Count

    I've also used:
    ' Last used row in entire spreadsheet.
    lngLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1

  10. The Following User Says Thank You to CephasOz For This Useful Post:

    HowardC (2012-01-20)

  11. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It depends on the situation really. Here there is probably no difference between CurrentRegion and my method. I don't use UsedRange because it is unreliable - what Excel thinks is used is not always accurate and the 'tricks' to reset it are not 100% reliable.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. The Following User Says Thank You to rory For This Useful Post:

    HowardC (2012-01-20)

  13. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Rory

    Thanks for the help, which is much appreciated

    I also played around with my code which now also works-see below


    Sub Totals()
    finalrow = Range("F65536").End(xlUp).Row
    Range("F" & finalrow + 3).Value = "Closing Balance"
    Range("g" & finalrow + 3).Formula = "=IF(RIGHT(G3,1)=""-"",SUBSTITUTE(G3,""-"","""")*-1,G3)"
    Range("F" & finalrow + 5).Value = "Input Tax"
    Range("G" & finalrow + 5).Formula = _
    "=SUMIFS(G6:G" & finalrow & ",G6:G" & finalrow & ","">0"",F6:F" & finalrow & ",""<>*SARS*"")"
    Range("F" & finalrow + 6).Value = "Output Tax"
    Range("G" & finalrow + 6).Formula = _
    "=SUMIFS(G6:G" & finalrow & ",G6:G" & finalrow & ",""<0"",F6:F" & finalrow & ",""<>*SARS*"")"

    Range("F" & finalrow + 7).Value = "Vat Payable"
    Range("g" & finalrow + 7).FormulaR1C1 = "=R[-2]C+R[-1]C"

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •