Sumifs with 2 criteria range

[djamdjam]

Hello

I try to use sumifs function with 2 criteria range, but it does'nt work. It works when I use jsut one range but Not with 2 ranges

Any help please?

Citeria Range 1 : {2015;2014}
Citeria Range 2 : {"Paris";"Madrid"}

=SOMPRODUCT(SUMIFS($A$2:$A$13;$B$2:$B$13;{2015;2014};$C$2:$C$13;{"Paris";"Madrid"}))




[TABLE="width: 264"]
<colgroup><col width="88" span="3" style="width:66pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 88"]Montant[/TD]
[TD="class: xl64, width: 88"]Year[/TD]
[TD="class: xl64, width: 88"]Town[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2000[/TD]
[TD="class: xl63, align: right"]2014[/TD]
[TD="class: xl63"]Paris[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3000[/TD]
[TD="class: xl63, align: right"]2016[/TD]
[TD="class: xl63"]London[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4000[/TD]
[TD="class: xl63, align: right"]2015[/TD]
[TD="class: xl63"]Madrid[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5000[/TD]
[TD="class: xl63, align: right"]2016[/TD]
[TD="class: xl63"]Madrid[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]6000[/TD]
[TD="class: xl63, align: right"]2015[/TD]
[TD="class: xl63"]London[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]7000[/TD]
[TD="class: xl63, align: right"]2015[/TD]
[TD="class: xl63"]London[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8000[/TD]
[TD="class: xl63, align: right"]2016[/TD]
[TD="class: xl63"]Paris[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]9000[/TD]
[TD="class: xl63, align: right"]2016[/TD]
[TD="class: xl63"]Madrid[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]10000[/TD]
[TD="class: xl63, align: right"]2015[/TD]
[TD="class: xl63"]Madrid[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]11000[/TD]
[TD="class: xl63, align: right"]2016[/TD]
[TD="class: xl63"]London[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12000[/TD]
[TD="class: xl63, align: right"]2014[/TD]
[TD="class: xl63"]Paris[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]13000[/TD]
[TD="class: xl63, align: right"]2015[/TD]
[TD="class: xl63"]Paris[/TD]
[/TR]
</tbody>[/TABLE]

Many Thanks
Djam

 

[pgc01]

Hi Djam
Welcome to the board

If you use 2 arrays for the criteria they must be orthogonal

In the English version, you'd use, for ex.:

{2015;2014} (vertical)
{"Paris","Madrid"} (horizontal)

Check the right syntax for your excel.

 

[djamdjam]

Yes!
Many thanks pgc !

 

[BGY23]

Just from curiosity pgc01 are you suggesting to create a new data matrix with vertical years and horizontal Towns? Orthogonal is new to me (I had to Google it LOL).

If thats the case then the solution lends itself to a pivot table?

 

[pgc01]

Djamdjam, I'm glad it helped.

 

[pgc01]

Just from curiosity pgc01 are you suggesting to create a new data matrix with vertical years and horizontal Towns? Orthogonal is new to me (I had to Google it LOL).

If thats the case then the solution lends itself to a pivot table?

Hi BGY

Orthogonal simply means perpendicular, at a 90 degree angle.

If you want to use the 2 criteria in djamdjam's formula then for ex. write

2015 and 2014 in a vertical vector, for ex. E1=2015, E2=2014
"Paris" and "Madrid" in a horizontal vector, for ex. F1="Paris", G1="Madrid"

=SOMPRODUCT(SUMIFS($A$2:$A$13;$B$2:$B$13;E1:E2;$C$2:$C$13;F1:G1))

Notice that you could use both horizontal or both vertical vectors but in that case you'd be matching the corresponding values at each position in the criteria vectors.

 

[BGY23]

Thanks so much for the reply.

Whilst I now understand and follow your formulas I couldn't equate your reply to an excel formula and thought you meant the OP should transpose his data to an othogonal matrix. But by placing the criteria in vertical and Horizontal matrix you achive the same result.

Thanks
Brian.

 

[pgc01]

Hi Brian

I'm glad it's clear now. Although you can use both parallel and perpendicular vectors for the 2 criteria it's important to understand that they answer 2 different questions.