SUMIFS multiple criteria cannot reference to a cell?

[danhendo888]

https://1drv.ms/x/s!AvjBsEPEq12ngTWjooUrD2AsRCFa?e=BpSibC

I am trying to do a SUMIFS formula calculating the sales column if the country equals australia, india and china (actual data has more countries)

=sum(sumifs(B2:B4,{

If I try to click on the cell to reference it, Excel doesn't let us.

I can just type out the country names but wondering if there's another way.

 

[steve the fish]

You dont need the {

 

[Nishant94]

Are you looking for something like this:

Book1
	A	B	C	D
1	Country	Sales
2	Australia	100
3	India	50		Sum of Australia,India,China
4	China	300		450
5	Pakistan	200
6	England	150
7	Germany	690
8
Sheet1

Cell Formulas
Range	Formula
D4	=SUM(SUMIF(A2:A7,{"Australia";"India";"China"},B2:B7))

 

[Nishant94]

If you dont want to type the name of the country then you can concatenate it using "&". Here is an example of it:

Book1
	A	B	C	D
1	Country	Sales
2	Australia	100
3	Pakistan	200		Sum of Australia,India,China
4	India	50		450
5	England	150
6	Germany	690
7	China	300
Sheet1

Cell Formulas
Range	Formula
D4	{=SUM(ISNUMBER(SEARCH(A2:A7,A2&A4&A7))*B2:B7)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[steve the fish]

Like i said in post number 2 he just doesnt need the {. He can then reference some cells with the countries required in. eg:

=SUMPRODUCT(SUMIFS(B:B,A:A,A2:A4))

where A2:A4 contains the countries to sum. These dont have to be within the criteria range but can be if required.

 

[danhendo888]

Thank you Nishant and Steve
Exactly what I was looking for