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

1	A	B
2	Account Description	THIS MONTH P & L
16	OPERAT. EXPENSES	11.260
17
18	GROSS OPERATING PROFIT	45.125
19
22	FINANCIAL EXPENSES	6.158
23
26	ADMINIS. EXPENSES	13.000
27
31	OTHER ADM. EXPENSES	8.104
32
33	NET PROFIT / LOSS	17.863

1	A	B
2	Account Description	THIS MONTH P & L

16	OPERAT. EXPENSES	11.260
17
18	GROSS OPERATING PROFIT	45.125
19
22	FINANCIAL EXPENSES	6.158
23
26	ADMINIS. EXPENSES	13.000
27
31	OTHER ADM. EXPENSES	8.104
32
33	TOTAL EXPENSES	38.522
34
35	NET PROFIT / LOSS	17.863

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