Sum with conditionals

[mickeystanford_alumni]

Hi all, hope good yourside.
I wanted to make a sumifs for this simple table with 3 columns, where Sales is the sum range, and year and country are the criteria range.
Though, I would like to have 3 possibilities where I'm struggling with:
- To sum all sales values for a certain year for 2 countries (China and the US).
- To sum all sales values for a certain year for all countries except Japan and Germany.

Any chance you could give me a hand there?

Cheers

Year	Country	Sales
2015​	US	113281.1​
2015​	Germany	86074.67​
2015​	China	75170.13​
2015​	Japan	58491.69​
2015​	UK	49316.31​
2015​	Brazil	48853.49​
2015​	India	48580.47​

 

[AlanY]

have you tried the sumifs() function?

 

[AlanY]

like this?

Book1
	A	B	C	D	E	F	G	H	I
1	Year	Country	Sales		Year	Country	Sales	xCountry	Sales
2	2015	US	113281		2015	US	188451	Japan	507348
3	2015	Germany	86074			China		Germany
4	2015	China	75170
5	2015	Japan	58491
6	2015	UK	49316
7	2015	Brazil	48853
8	2015	India	48580
9	2016	US	11328.1
10	2016	Germany	8607.4
11	2016	China	7517
12	2016	Japan	5849.1
13	2016	UK	4931.6
14	2016	Brazil	4885.3
15	2016	India	4858
Sheet1

Cell Formulas
Range	Formula
G2	G2	=SUMIFS(C2:C15,A2:A15,E2,B2:B15,F2)+SUMIFS(C2:C15,A2:A15,E2,B2:B15,F3)
I2	I2	=SUMIF(A2:A15,E2,C2:C15)-SUMIFS(C2:C15,A2:A15,E2,B2:B15,H2)+SUMIFS(C2:C15,A2:A15,E2,B2:B15,H3)

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$A$8:$A$9

 

[mickeystanford_alumni]

Hi AlanY, is there any way to make it <>"Japan"&"Germany"
Something like that inside the sumifs function?

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Year	Country	Sales		Year	Country	Sales	xCountry	Sales
2	2015	US	113281		2015	US	188451	Japan	335200
3	2015	Germany	86074			China		Germany
4	2015	China	75170
5	2015	Japan	58491
6	2015	UK	49316
7	2015	Brazil	48853
8	2015	India	48580
9	2016	US	11328.1
10	2016	Germany	8607.4
11	2016	China	7517
12	2016	Japan	5849.1
13	2016	UK	4931.6
14	2016	Brazil	4885.3
15	2016	India	4858
View

Cell Formulas
Range	Formula
G2	G2	=SUM(SUMIFS(C:C,A:A,E2,B:B,F2:F3))
I2	I2	=SUMIFS(C:C,A:A,E2)-SUM(SUMIFS(C:C,A:A,E2,B:B,H2:H3))

 

[AlanY]

spoke too soon

 

[mickeystanford_alumni]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Year	Country	Sales		Year	Country	Sales	xCountry	Sales
2	2015	US	113281		2015	US	188451	Japan	335200
3	2015	Germany	86074			China		Germany
4	2015	China	75170
5	2015	Japan	58491
6	2015	UK	49316
7	2015	Brazil	48853
8	2015	India	48580
9	2016	US	11328.1
10	2016	Germany	8607.4
11	2016	China	7517
12	2016	Japan	5849.1
13	2016	UK	4931.6
14	2016	Brazil	4885.3
15	2016	India	4858
View

Cell Formulas
Range	Formula
G2	G2	=SUM(SUMIFS(C:C,A:A,E2,B:B,F2:F3))
I2	I2	=SUMIFS(C:C,A:A,E2)-SUM(SUMIFS(C:C,A:A,E2,B:B,H2:H3))

Hi Fluff, txs a lot. Tx also to AlanY.

I was wondering if for the formula I2 you put, I could also include 2015 and 2016 in the same way you did with H2:H3. I am trying but not working.

Txx again

 

[mickeystanford_alumni]

That'd mean,
=SUMIFS(C:C,A:A,E2)-SUM(SUMIFS(C:C,A:A,H5:H6,B:B,H2:H3)

where H5:H6 is 2015, 2016

however just giving me the number of 2015 for the final result

 

[Fluff]

How about

=SUM(SUMIFS(C:C,A:A,E2:E3))-SUM(SUMIFS(C:C,A:A,TRANSPOSE(E2:E3),B:B,H2:H3))

 

[mickeystanford_alumni]

Brilliant! Thxs a lot