Sum based on description criteria

Panoos64 · Sep 2, 2017

Hi all, I would like to SUM the below categories based on texts description criteria as my below extract using vba.
OPERAT. EXPENSES + FINANCIAL EXPENSES + ADMINIS. EXPENSES + OTHER ADM. EXPENSES
Many thanks in advance



16
17
18
19
22	FINANCIAL EXPENSES
23
26	ADMINIS. EXPENSES
27
31	OTHER ADM. EXPENSES
32
33	NET PROFIT / LOSS

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="class: xl65, align: center"]B[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Account Description[/TD]
[TD="class: xl66, align: center"]THIS MONTH P & L[/TD]

[TD="class: xl64"]OPERAT. EXPENSES[/TD]
[TD="class: xl69, align: right"]11.260[/TD]

[TD="class: xl65, align: right"][/TD]

[TD="class: xl63, width: 205"]GROSS OPERATING PROFIT[/TD]
[TD="class: xl65, align: right"]45.125[/TD]

[TD="class: xl65, align: right"][/TD]

[TD="class: xl65, align: right"]6.158[/TD]

[TD="class: xl65, align: right"][/TD]

[TD="class: xl65, align: right"]13.000[/TD]

[TD="class: xl65, align: right"][/TD]

[TD="class: xl65, align: right"]8.104[/TD]

[TD="class: xl65, align: right"][/TD]

[TD="class: xl65, align: right"]17.863[/TD]

</tbody>




16
17
18
19
22	FINANCIAL EXPENSES
23
26	ADMINIS. EXPENSES
27
31	OTHER ADM. EXPENSES
32
33	TOTAL EXPENSES
34
35	NET PROFIT / LOSS

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="class: xl67, align: center"]B[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Account Description[/TD]
[TD="class: xl68, align: center"]THIS MONTH P & L[/TD]

[TD="class: xl66"][/TD]
[TD="class: xl69, align: right"][/TD]

[TD="class: xl66, width: 205"]OPERAT. EXPENSES[/TD]
[TD="class: xl71, width: 83, align: right"]11.260[/TD]

[TD="class: xl67, align: right"][/TD]

[TD="class: xl65, width: 205"]GROSS OPERATING PROFIT[/TD]
[TD="class: xl67, align: right"]45.125[/TD]

[TD="class: xl67, align: right"][/TD]

[TD="class: xl67, align: right"]6.158[/TD]

[TD="class: xl67, align: right"][/TD]

[TD="class: xl67, align: right"]13.000[/TD]

[TD="class: xl67, align: right"][/TD]

[TD="class: xl67, align: right"]8.104[/TD]

[TD="class: xl67, align: right"][/TD]

[TD="class: xl67, align: right"] 38.522 [/TD]

[TD="class: xl67, align: right"][/TD]

[TD="class: xl67, align: right"]17.863[/TD]

</tbody>

Michael M · Sep 2, 2017

Try

Code:

=SUMIF(A16:A35,"*expenses*",B16:B35)

Panoos64 · Sep 3, 2017

Hi Michael, I tried and i installed the formula in a specific cell "B33", but the rows are not stable and thats why i prefer vba. Also i need to insert a description (TOTAL EXPENSES) in col "A" between OTHER ADM. EXPENSES and NET PROFIT / LOSS. However if is difficult do not worry. I will do it by manual method and apologies that i did not clarify in my above desciption. However many thanks for all support you done for me. Have a nice day

MickG · Sep 3, 2017

Try this:-

Code:

[COLOR="Navy"]Sub[/COLOR] MG03Sep05
[COLOR="Navy"]Dim[/COLOR] Nams [COLOR="Navy"]As[/COLOR] Variant, N [COLOR="Navy"]As[/COLOR] Variant, oSum [COLOR="Navy"]As[/COLOR] Double, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Nams = Array("OPERAT. EXPENSES", "Financial EXPENSES", "ADMINIS. EXPENSES", "OTHER ADM. EXPENSES")
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] N [COLOR="Navy"]In[/COLOR] Nams: Dic(N) = Empty: [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            oSum = oSum + Dn.Offset(, 1).Value
            c = c + 1
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] c = Dic.Count [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]With[/COLOR] Dn.Offset(1)
                .Resize(2).EntireRow.Insert
                .Offset(-1).Value = "TOTAL EXPENSES"
                .Offset(-1, 1).Value = oSum
            [COLOR="Navy"]End[/COLOR] With
             [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Panoos64 · Sep 4, 2017

Thank you so much Mick! It works perfect and is what exactly i was required. Thanks once again. Hv a nice day

Panoos64 · Sep 4, 2017

Mick, how can i develop or combine the above code so that to "SUM" also the col. D, F, H, J. Many thanks and apologies that i bother you again.
Hv a nice day

MickG · Sep 5, 2017

Try this:-

Code:

[COLOR="Navy"]Sub[/COLOR] MG05Sep22
[COLOR="Navy"]Dim[/COLOR] Nams [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oSum(1 To 5) [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Nams = Array("OPERAT. EXPENSES", "Financial EXPENSES", "ADMINIS. EXPENSES", "OTHER ADM. EXPENSES")
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] n [COLOR="Navy"]In[/COLOR] Nams: Dic(n) = Empty: [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Dic.Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            oSum(1) = oSum(1) + Dn.Offset(, 1).Value
            oSum(2) = oSum(2) + Dn.Offset(, 3).Value
            oSum(3) = oSum(3) + Dn.Offset(, 5).Value
            oSum(4) = oSum(4) + Dn.Offset(, 7).Value
            oSum(5) = oSum(5) + Dn.Offset(, 9).Value
            c = c + 1
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]If[/COLOR] c = Dic.Count [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]With[/COLOR] Dn.Offset(1)
                .Resize(2).EntireRow.Insert
                .Offset(-1).Value = "TOTAL EXPENSES"
                .Offset(-1, 1).Value = oSum(1)
                .Offset(-1, 3).Value = oSum(2)
                .Offset(-1, 5).Value = oSum(3)
                .Offset(-1, 7).Value = oSum(4)
                .Offset(-1, 9).Value = oSum(5)
            [COLOR="Navy"]End[/COLOR] With
             [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Panoos64 · Sep 5, 2017

Perfect indeed Mick! I appreciated so much for all support you done for me and your time, working for my project. All the best to you.
Have a great, lovely day

MickG · Sep 5, 2017

You're welcome

Sum based on description criteria

Panoos64

Well-known Member

Excel Facts

Michael M

Well-known Member

Panoos64

Well-known Member

MickG

MrExcel MVP

Panoos64

Well-known Member

Panoos64

Well-known Member

MickG

MrExcel MVP

Panoos64

Well-known Member

MickG

MrExcel MVP

Similar threads

Forum statistics

Share this page

Sum based on description criteria

Well-known Member

Excel Facts

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock