Finding out Sum value of specific transaction type

[uday]

Hi,

In sheet1 of given attachment I want the Sum value of cash and cash equivalent type.

Sheet2 has all the data. I want all the sum value in segregated manner, like total cash/USD,Cash/CAD etc. also the total of cash equivalent.

I want a sumif function with VBA code. Please assist.

 

[Chihiro]

Quick and dirty code.

Sub sTotal()
Dim sws As Worksheet
Dim rws As Worksheet
Dim lsRow As Long
Dim sRange As Range
Dim cRange As Range
Set sws = ThisWorkbook.Sheets("Sheet2")
Set rws = ThisWorkbook.Sheets("Sheet1")
lsRow = sws.Range("A" & Rows.Count).End(xlUp).Row

With sws
Set sRange = .Range("B2:B" & lsRow)
Set cRange = .Range("A2:A" & lsRow)
rws.Cells(3, 3) = Application.WorksheetFunction.SumIfs(sRange, cRange, "*USD")
rws.Cells(4, 3) = Application.WorksheetFunction.SumIfs(sRange, cRange, "*CAD")
rws.Cells(5, 3) = Application.WorksheetFunction.SumIfs(sRange, cRange, "*EUR")
rws.Cells(6, 3) = Application.WorksheetFunction.SumIfs(sRange, cRange, "*SGD")
rws.Cells(7, 3) = Application.WorksheetFunction.Sum(rws.Range("C3:C6"))
rws.Cells(9, 3) = Application.WorksheetFunction.SumIfs(sRange, cRange, "Cash Equivalent")
End With

End Sub

 

[uday]

Thanks for your reply. I want to know one more thing about the same topic. If the cell reference of sheet2 is not fixed then what will be the best code to get the data in sheet1.

I am getting this raw data every day and the cell reference is not fixed in the sheet2 hence I need a dynamic data which will pick up the sum value of any specific type as per the name.i.e, cash/cash equivalent and paste it into sheet1.

 

[Chihiro]

Can you give me couple of different arrangement the data could come in for sheet2? Will it be in different columns or always in same column for the data that need to be summed up?

Also, are you opposed to having different layout for Sheet1?

 

[uday]

Yes Sure. No,in Sheet2 all the column will be same, but the rows can be different as per the data availability and same thing will go for sheet1 as well.

 

[Chihiro]

Here you go. Sample workbook attached.

Sub sTotal()
Dim sws As Worksheet
Dim rws As Worksheet
Dim lsRow As Long
Dim lrRow As Long
Dim bRow As Long
Dim sRange As Range
Dim cRange As Range


Set sws = ThisWorkbook.Sheets("Sheet2")
Set rws = ThisWorkbook.Sheets("Sheet1")
lsRow = sws.Range("A" & Rows.Count).End(xlUp).Row
lrRow = rws.Range("A" & Rows.Count).End(xlUp).Row

With rws
Set sRange = sws.Range("B2:B" & lsRow)
Set cRange = sws.Range("A2:A" & lsRow)
For i = 3 To lrRow
    If .Cells(i, 1) <> "Cash" Then
        c = .Cells(i, 1)
        .Cells(i, 3) = Application.WorksheetFunction.SumIfs(sRange, cRange, "*" & c)
    End If
 
Dim aa As String, ba As String, sr As String
    If .Cells(i, 2) = "Sum Total" Then
        ba = .Cells(i - 1, 3).Address
        aa = .Cells(3, 3).Address
        sr = aa & ":" & ba
        .Cells(i, 3) = Application.WorksheetFunction.Sum(.Range(sr))
    End If
Next i
End With

End Sub

 

[uday]

Thanks again for the wonderful code, but I am getting an unwanted sum total in sheet 1. That is total of cash equivalent and sum of all currency total.
Please find the below screenshot for the reference. I have highlighted with the red colour.

 

[Chihiro]

Ah, I didn't test for blank row in between. Revised code below.

Sub sTotal()
Dim sws As Worksheet
Dim rws As Worksheet
Dim lsRow As Long
Dim lrRow As Long
Dim bRow As Long
Dim sRange As Range
Dim cRange As Range


Set sws = ThisWorkbook.Sheets("Sheet2")
Set rws = ThisWorkbook.Sheets("Sheet1")
lsRow = sws.Range("A" & Rows.Count).End(xlUp).Row
lrRow = rws.Range("A" & Rows.Count).End(xlUp).Row

With rws
Set sRange = sws.Range("B2:B" & lsRow)
Set cRange = sws.Range("A2:A" & lsRow)
For i = 3 To lrRow
    If .Cells(i, 1) <> "Cash" And .Cells(i, 1) <> "" Then
        c = .Cells(i, 1)
        .Cells(i, 3) = Application.WorksheetFunction.SumIfs(sRange, cRange, "*" & c)
    End If
   
Dim aa As String, ba As String, sr As String
    If .Cells(i, 2) = "Sum Total" Then
        ba = .Cells(i - 1, 3).Address
        aa = .Cells(3, 3).Address
        sr = aa & ":" & ba
        .Cells(i, 3) = Application.WorksheetFunction.Sum(.Range(sr))
    End If
Next i
End With

End Sub

 

[uday]

Thank you very much chihiro.

 

[Marc L]

Hi !

Chihiro, another way :​

Sub Demo()
With Sheet2.UsedRange.Columns
    F1$ = "SUMIF(" & .Item(1).Address(External:=True) & ",""*"
    F2$ = """," & .Item(2).Address(External:=True) & ")"
End With
                        Application.ScreenUpdating = False
With Sheet1
    For R& = 3 To .UsedRange.Rows.Count
        Select Case .Cells(R, 2).Value
               Case "Total":      .Cells(R, 3).Value = Evaluate(F1 & .Cells(R, 1).Value & F2)
               Case "Sum Total":  .Cells(R, 3).Value = Evaluate("SUM('" & .Name & "'!C3:C" & R - 1 & ")")
        End Select
    Next
End With
                        Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

 

[Chihiro]

@Marc L
Nice and tidy code. I'll have to learn how to concatenate better in VBA.
And start using "Case". Thanks for the example, I'll go through it and see how to construct this type of code.

 

[Marc L]

Thanks Chihiro !

Two rules when coding in VBA, in this case, it's the first one :
TEBV : Think Excel Before VBA !

So I started with a formula in a worksheet before to begin to code …
Separate fix parts to variable ones …
Then using powerful VBA Evaluate method, it's done !

The key here is not to check first column but B column …

Obvious second rule is TBTO : Think, But Think Object !
Meaning just respect Excel object model like
Application, Workbook, Worksheet, Cells or Range, methods & properties …
​

 

[Marc L]

Illustrating TBTO rule with "Sum Total" :

whatever the active worksheet, my Demo procedure works
'cause of the worksheet name within the Evaluate formula.

This formula can be reduced :​

Case "Sum Total":  .Cells(R, 3).Value = .Evaluate("SUM(C3:C" & R - 1 & ")")

But try without the dot before Evaluate statement
with Sheet2 as active worksheet : wrong result to zero !
Right only if Sheet1 is active … Why ?

Without the dot, Evaluate is equal to Application.Evaluate
(as written in VBA help) and 'cause there is no worksheet reference
in the formula, so the range refers to active worksheet …

With the dot, Evaluate refers to previous statement With object :
Sheet1 codename and works whatever the active worksheet,
like there is a worksheet name in formula.

Think ! But Think Object …​