SUMIFS with changing criteria

[Alpana Savarna]

Hi all,

So here is what I want to do. In the file attached, depending on the zone the user chooses, I want to give the total output. Countries for each zone are defined separately. So in the current example, since Asia is chosen, the output should be 173. I could write a formula which says

=IF(A2="Asia"; SUMIFS(B5:B14;A5;A14;D5;A5;A14;D6); IF(A2 = "Europe";...)
And you get the gist.

Is there a faster way to do this?
Thanks for your responses.

Regards,
Alpana

[misrasomendra]

Hi,

Can you upload this in a sample file.

[misrasomendra]

See the file for a shorter sample. Yellow cell has a formula.

[Alpana Savarna]

Thanks a bunch Somendra.
That was very helpful.

SUMPRODUCT is something I need some getting used to. Cannot truly understand it. As for the COUNTIF, could you take out some time to explain what is happening there please? I see that A4:A9 is provided as criteria. Shouldn't it be the range from which we want to choose the countries which satisfy our criteria?

Thanks a bunch in advance.

[misrasomendra]

Hi Alpana,

=SUMPRODUCT(B4:B9*(COUNTIF(INDEX(G3:I4,,MATCH(A2,G2:I2,0)),A4:A9)))

Here Red color is the range to sum, meeting the criteria of countries within the selected option.

SUMPRODUCT is basically add the multiplication of two or more array of numbers.

Say you have an array like {1;2;3} and another array like {2;2;2} and you want a total like 1*2+2*2+3*2, so you will enter it like this =A1*B1+A2*B2+A3*B3. This will give you the result. Or second method of doing it creating a helper column C1 = A1*B1; C2 = A2*B2; C3=A3*B3 and then sum up these.

Both these can be avoided with the help of =SUMPRODUCT(A1:A3,B1:B3) or =SUMPRODUCT(A1:A3*B1:B3). So SUMPRODUCT will give you the same result.

Here I want to mention that if you are using , as a separator in SUMPRODUCT than both the array should be of same dimensions but if you are multiplying inside SUMPRODUCT, they can be of variable dimension.

Now in our case we need to sum so we need two array.
1. Array of values which is B4:B9
2. An array of 0 & 1. 0 for countries outside selected option. 1 for countries in the selected option.

So COUNTIF is used for making the array of 0 & 1. Normally we pass single criteria in COUNTIF, but if you pass an array you will get an array as output.

Size of array will be number of element in criteria array. Like I Have an array of {A;B;C;D;E} and I want to count A & B, so if I will write; =COUNTIF(A1:A5,B1:B5) where A1:A5 holds A,B,C,D,E and B1:B2 holds A,b I will get a result of {1;1}, because both count in the array is 1.

But if I reverse this =COUNTIF(B1:B5,A1:A5) than COUNTIF will check for A,b,c,d,e in A,B. So the result will be {1;1;0;0;0}.

That's why I had reversed the range & criteria in COUNTIF so that I can get an array of same dimension as of the values so than SUMPRODUCT will multiply the values with 0 & 1 and than finally add's it up.

Hope, this explanation is clear to you, otherwise write back I will try to clear it up.

And if your query is solved, than don't forget to mark the thread as SOLVED and if you feel the solution had helped than you may give me some reputation points by clicking the Star * below my comments.

[Alpana Savarna]

Thanks a bunch Somendra, I understood it.
You are brilliant at explanations. I can see you really put a lot of effort there. I truly appreciate it.

Really, thanks a lot.

[misrasomendra]

Glad you solved it, Welcome Back. You can mark the thread as solved and give me some rep points by clicking the stars below my comment if you feel.