Sumifs - Using Multiple Criteria and Multiple Values

[JSH10]

I want to return the Total Revenue from the below table (Table1), where Colour is = R & W, Item = A & B and Size = S & M. I tried using the formula below but it doesn't return the correct value. Adding multiple values to the third criteria range is the problem, it ignores all bar the first value in Criteria3. I assumed that the separator in Criteria2 ... CriteriaN would be ;

=SUM(SUMIFS(Table1[Revenue],Table1[Colour],{"R","W"},Table1[Item],{"A";"B"},Table1[Size],{"S";"M"}))

Any help would be greatly appreciated.

Thanks,
Jon

Colour	Item	Size	Revenue
R	A	S	100​
R	A	M	200​
R	A	L	300​
W	A	S	400​
W	A	M	500​
W	A	L	600​
B	A	S	700​
B	A	M	800​
B	A	L	900​
R	B	S	1000​
R	B	M	1100​
R	B	L	1200​
W	B	S	1300​
W	B	M	1400​
W	B	L	1500​
B	B	S	1600​
B	B	M	1700​
B	B	L	1800​
R	C	S	1900​
R	C	M	2000​
R	C	L	2100​
W	C	S	2200​
W	C	M	2300​
W	C	L	2400​
B	C	S	2500​
B	C	M	2600​
B	C	L	2700​

 

[NickFru]

Hello, try;

=SUM(SUMIFS(D2:D28, A2:A28,{"R","W"},B2:B28,{"A","B"},C2:C28,{"S","M"}))

 

[NickFru]

Hello - apologies, ignore, previous post,try this;

=SUM(SUMIFS(D2:D28,A2:A28, F2:F3, B2:B28, F5:F6, C2:C28, F8:F9))

F2:F3 is W and A for the colours, F5:F6 is A and B for the items, F8:F9 is L and S for the sizes.

 

[Alex Blakenburg]

Try:

=SUM(Table1[Revenue]*((Table1[Colour]="R")+(Table1[Colour]="W"))*((Table1[Item]="A")+(Table1[Item]="B"))*((Table1[Size]="S")+(Table1[Size]="M")))

For ease of interpretation you can show it this way:

=SUM(Table1[Revenue]
               *((Table1[Colour]="R") + (Table1[Colour]="W"))
               *((Table1[Item]="A")     + (Table1[Item]="B"))
               *((Table1[Size]="S")      + (Table1[Size]="M")))

 

[Fluff]

Hi & welcome to MrExcel.

Adding multiple values to the third criteria range is the problem, it ignores all bar the first value in Criteria3

All the xxxIFS function can only accept 2 arrays (one vertical & one horizontal). So when you have 3 or more arrays, you need to do it like Alex has shown.

 

[JSH10]

Try:

=SUM(Table1[Revenue]*((Table1[Colour]="R")+(Table1[Colour]="W"))*((Table1[Item]="A")+(Table1[Item]="B"))*((Table1[Size]="S")+(Table1[Size]="M")))

For ease of interpretation you can show it this way:

=SUM(Table1[Revenue]
               *((Table1[Colour]="R") + (Table1[Colour]="W"))
               *((Table1[Item]="A")     + (Table1[Item]="B"))
               *((Table1[Size]="S")      + (Table1[Size]="M")))

Thanks Alex

 

[JSH10]

Hello - apologies, ignore, previous post,try this;

=SUM(SUMIFS(D2:D28,A2:A28, F2:F3, B2:B28, F5:F6, C2:C28, F8:F9))

F2:F3 is W and A for the colours, F5:F6 is A and B for the items, F8:F9 is L and S for the sizes.

Hi Nick, thanks for the reply. Unfortunately this didn't work, however, I was able to resolve the problem using sumproduct.

 

[NickFru]

Ah no problem at all - as Fluff explained, I tried to do the SUMIFS based on more than two arrays which ultimately didn't work but Alex's method worked perfectly when I tested it myself on the extracted data so in hindsight, I should have used the SUMPRODUCT instead. We learn something every day I suppose!! Take care.