SumIfs multiple criteria & columns - UDF solution

[illusionek]

Hello

I got a challenge that requires SumIfs on steroids I need to be able to sum multiple columns applying two criteria on rows and one on column. The data set will be dynamic (both rows & columns) and I am unable to add helper columns. Ideally I would like to avoid array formulas due to concerns about performance but if I have no choice then beggars cant be choosers

I think VBA user-defined function would be ideal solutions but I struggle to come up with the correct code as my VBA skills are very limited.

First table provides example of data structure and the bottom two criteria applied and expected result.

Item	Region	Jan	Feb	Mar
Grapes	North	100​	200​	250​
Grapes	South	50​	2​	54​
Apples	North	454​	54​	4​
Apples	South	454​	44​	44​
Grapes	South	44​	5​	555​
Apples	North	4​	5​	2​

Item	Grapes
Region	South
Month	Feb
Expected Result	7​

Item	Apples
Region	North
Month	Mar
Expected Result	6​

 

[Marcelo Branco]

Try

A solution using a regular formula...

Pasta1
	A	B	C	D	E	F
1	Item	Region	Jan	Feb	Mar
2	Grapes	North	100	200	250
3	Grapes	South	50	2	54
4	Apples	North	454	54	4
5	Apples	South	454	44	44
6	Grapes	South	44	5	555
7	Apples	North	4	5	2
8
9
10
11
12	Item	Grapes	Apples
13	Region	South	North
14	Month	Feb	Mar
15
16	Expected Result	7	6
17
Plan1

Cell Formulas
Range	Formula
B16:C16	B16	=SUMIFS(INDEX($C$2:$E$7,0,MATCH(B14,$C1:$E1,0)),$A2:$A7,B12,$B2:$B7,B13)

M.

 

[illusionek]

It works well, many thanks.

Would there be any difference in performance between combination of SumIfs&Index&Match and below, which is what I managed to come up with? The main reason I was after UFD formula was due to performance concerns as I will have about 80 columns and 1000 rows. I have bad experience of using Sumproduct due to poor performance in the past.

The final report will have multiple tabs with formulas etc and my concern is that once they all start recalculating then the performance will hit the floor.


=SUMPRODUCT(C2:E7*(A2:A7=I1)*(B2:B7=I2)*(C1:E1=I3))

 

[Marcelo Branco]

The main reason I was after UFD formula was due to performance concerns as I will have about 80 columns and 1000 rows.

The formula I've suggested shouldn't present any performance issues.

M.