Adding Multiple SUMIFS In VBA

[excelbytes]

I have two or three SUMIFS that I need to add together into one cell using VBA. An example of two formulas I need to add together (data is in tables):

Range("B7").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(PeticionesTable[Efectivo],PeticionesTable[Fecha de entrega (mes/dia/año)],"">=""&R1C2,PeticionesTable[Fecha de entrega (mes/dia/año)],""<=""&R2C2,PeticionesTable[Línea estratégica],RC[-1])"

SUM with this:

"=SUMIFS(PeticionesTable[Especie],PeticionesTable[Fecha de entrega (mes/dia/año)],"">=""&R1C2,PeticionesTable[Fecha de entrega (mes/dia/año)],""<=""&R2C2,PeticionesTable[Línea estratégica],RC[-1])"

As you can see, all the criteria is the same, the only difference is the sum_range. How do I combine these into one formula?

 

[severynm]

Why do you need VBA? Can you just add the two sumifs together in a cell?

=SUMIFS(PeticionesTable[Efectivo],PeticionesTable[Fecha de entrega (mes/dia/año)],"">=""&R1C2,PeticionesTable[Fecha de entrega (mes/dia/año)],""<=""&R2C2,PeticionesTable[Línea estratégica],RC[-1]) + SUMIFS(PeticionesTable[Especie],PeticionesTable[Fecha de entrega (mes/dia/año)],"">=""&R1C2,PeticionesTable[Fecha de entrega (mes/dia/año)],""<=""&R2C2,PeticionesTable[Línea estratégica],RC[-1])

 

[excelbytes]

severynm, the inclusion of adding two SUMIFS in a cell is part of a much larger VBA process, which includes combining tables, adding a worksheet, entering various labels and entering these formulas. At this point, I just entered separate SUMIFS in different cells and then a SUM function in a separate cell. I was just curious as to why I couldn't do in VBA what you had indicated - adding the two together with a plus sign.

 

[severynm]

Understood. I'm not sure why it doesn't work in VBA - I threw together some sample data and got a consistent result - result1 and result2 both equal 37 as they should:

a	aa	1
b	bb	2
a	aa	3
b	bb	4
a	a	5
b	b	6
c	c	7
a	aa	8
b	bb	9
c	cc	10

Sub temp()
    Dim suma As Integer, sumb As Integer, sumc As Integer, result1 As Integer, result2 As Integer
    
    suma = Application.WorksheetFunction.SumIfs(Sheet2.Range("E9:E18"), Sheet2.Range("C9:C18"), "a", Sheet2.Range("D9:D18"), "aa")
    sumb = Application.WorksheetFunction.SumIfs(Sheet2.Range("E9:E18"), Sheet2.Range("C9:C18"), "b", Sheet2.Range("D9:D18"), "bb")
    sumc = Application.WorksheetFunction.SumIfs(Sheet2.Range("E9:E18"), Sheet2.Range("C9:C18"), "c", Sheet2.Range("D9:D18"), "cc")
    result1 = suma + sumb + sumc
    
    result2 = Application.WorksheetFunction.SumIfs(Sheet2.Range("E9:E18"), Sheet2.Range("C9:C18"), "a", Sheet2.Range("D9:D18"), "aa") + _
        Application.WorksheetFunction.SumIfs(Sheet2.Range("E9:E18"), Sheet2.Range("C9:C18"), "b", Sheet2.Range("D9:D18"), "bb") + _
        Application.WorksheetFunction.SumIfs(Sheet2.Range("E9:E18"), Sheet2.Range("C9:C18"), "c", Sheet2.Range("D9:D18"), "cc")
    
End Sub

 

[Marc L]

​

In case of speed concern try SUMPRODUCT rather than SUMIFS …​

 

[excelbytes]

Thanks, I'll try again and see what happens

 

[Lipcov2011]

Hi Dear All, please help to build VBA cod and construct sumifs in P&L sheet 2 using sumifs formula= SUMIFS(Sheet1!$F:$F,Sheet1!$G:$G,$A2,Sheet1!$A:$A,B$1)

Cost Center	INCOME	EXPENSE	From billing period	To billing Period	PROFIT/LOSS	IFRS Mapping1	IFRS Mapping2
Astrology	5.98​	0.00​	31/08/2014​	31/08/2014​	0.00​	Finance revenue	Finance revenue
Astrology	0.00​	12.63​	28/08/2014​	29/08/2014​	0.00​	Cost of sales	Cost of sales
Astrology	0.00​	1,076.95​	01/08/2014​	31/08/2014​	0.00​	Cost of sales	Cost of sales
Astrology	0.00​	227.27​	29/08/2014​	29/08/2014​	7.35​	Other expenses	Other expenses
Astrology	0.00​	8.76​	01/08/2014​	31/08/2014​	5,683.67​	Administrative expenses	Administrative expenses
Astrology	0.00​	48.18​	14/08/2014​	14/08/2014​	982.90​	Cost of sales	Cost of sales
Astrology	0.00​	38.36​	14/08/2014​	14/08/2014​	55.84​	Cost of sales	Cost of sales
Astrology	0.00​	120.00​	01/08/2014​	31/08/2014​	1,115.55​	Cost of sales	Cost of sales
Astrology	0.00​	28.18​	14/08/2014​	14/08/2014​	4.50​	Cost of sales	Cost of sales
Astrology	0.00​	6.34​	01/08/2014​	31/08/2014​	96.70​	Administrative expenses	Administrative expenses
Astrology	0.00​	33.16​	14/08/2014​	14/08/2014​	72.62​	Cost of sales	Cost of sales

Sheet 2 (Pl statement)

Detail

Astrology

DirectSales

IndirectSales

Intermediary

JV

MobileMarketing

SmsArabic

SmsCchat

SmsEnglish

SmsFrench

SMSRow

SmtphoneApp

Telecomm HK

TOTAL

Google revenue

22.5​

2021.91​

7​

0​

80.66​

0​

0​

0​

4​

120.99​

1026​

4456.88​

0​

Rendering of services

4.5​

320753.4​

62113.51​

10025.82​

21.75​

20.5​

1895.39​

0​

437.52​

1313.65​

2311.97​

44568.8​

0​

Revenue

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Cost of sales

2337.33​

240788.1​

2793992​

4056.83​

2022.1​

750.18​

1000.09​

0​

6916.59​

674.77​

155144.5​

52939.44​

-60000​

Gross profit

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

GM

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Administrative expenses

6103.92​

15811.65​

1913480​

841.66​

812.44​

8​

105.64​

0​

286.96​

5203.62​

724.96​

8913.76​

0​

Other expenses

25.35​

25529.26​

7​

0​

4.5​

0​

14.77​

0​

9​

44.52​

86.11​

4456.88​

0​

Net impact of FX diff between Finance and accountancy

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Finance revenue

169.84​

55.72​

908​

0​

4​

0​

0​

0​

25​

4​

4456.89​

8913.76​

0​

Finance costs

1125.03​

40.31​

322.19​

0​

0​

0​

0​

0​

8.5​

0.01​

8913.76​

-55000​

0​

Financial result

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Profit before tax

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Income tax expense estimated

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Profit for the month

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

Grand total

9788.47​

605000.4​

4770829​

14924.31​

2945.45​

778.68​

3015.89​

0​

7687.57​

7361.56​

172664.1​

69249.52​

-60000​

5604245​