Results 1 to 10 of 10
Thread: Sumifs Formula

20120119, 11:22 #1
 Join Date
 Feb 2008
 Posts
 1,550
 Thanks
 138
 Thanked 11 Times in 11 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

20120119, 11:51 #2
 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.

20120119, 12:35 #3
 Join Date
 Feb 2008
 Posts
 1,550
 Thanks
 138
 Thanked 11 Times in 11 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

20120119, 14:17 #4
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Here is a nonVBA 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

20120119, 20:30 #5
 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 NonSARS 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

20120119, 21:26 #6
 Join Date
 Feb 2008
 Posts
 1,550
 Thanks
 138
 Thanked 11 Times in 11 Posts
SUMIFS Formula
Thanks for the help, much appreciated

20120120, 06:25 #7
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,330
 Thanks
 3
 Thanked 218 Times in 201 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

The Following User Says Thank You to rory For This Useful Post:
CephasOz (20120120)

20120120, 09:45 #8
 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

The Following User Says Thank You to CephasOz For This Useful Post:
HowardC (20120120)

20120120, 09:50 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,330
 Thanks
 3
 Thanked 218 Times in 201 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

The Following User Says Thank You to rory For This Useful Post:
HowardC (20120120)

20120120, 12:06 #10
 Join Date
 Feb 2008
 Posts
 1,550
 Thanks
 138
 Thanked 11 Times in 11 Posts
Hi Rory
Thanks for the help, which is much appreciated
I also played around with my code which now also workssee 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