1. ## 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

2. ## 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. ## 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. 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")

5. 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
ColNoToColStr = Replace(vVal(1), ":", vbNullString)
End Function```

6. ## SUMIFS Formula

Thanks for the help, much appreciated

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

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

CephasOz (2012-01-20)

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

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

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

HowardC (2012-01-20)

13. 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
•