Sumproduct Query

[mart_mrexcel]

I have a formula:

SUMPRODUCT((PaygroupCode=B2)*(Nationality= {"Nepal","Sudan","Chinese","Japanese","African","British","American"}) *(TotalSalary))

My Query:
How can I put the: {"Nepal","Sudan","Chinese","Japanese","African","British","American"} in a list and name it in the "Name Manager" instead of writing it 1 by 1 in the formula.

Thanks

 

[mrhstn]

Try in the name manager create a new defined name and then in refers to use ={"Nepal","Sudan","Chinese","Japanese","African","British","American"}

 

[kvsrinivasamurthy]

Enter
E1=Nepal
E2=Sudan
E3=Chinese
.
.
.E7=American
Fotrmula is
=SUMPRODUCT((PaygroupCode=B2)*COUNTIF(E:E7,Nationality) *(TotalSalary))

 

[Aladin Akyurek]

Try in the name manager create a new defined name and then in refers to use ={"Nepal","Sudan","Chinese","Japanese","African","British","American"}

Enter this subset of nationalities in a convenient range and name the range Nsubset using the Name Box or the Name Manager.

Then invoke either...

=SUMPRODUCT(TotalSalary,(PaygroupCode=B2)*ISNUMBER(MATCH(Nationality,Nsubset,0))

or

=SUMPRODUCT(SMIFS(TotalSalary,PaygroupCode,B2,Nationality,Nsubset))

 

[mart_mrexcel]

Thanks a lot that helps

 

[Aladin Akyurek]

Thanks a lot that helps

You are welcome. For the record, there are some typo's...

=SUMPRODUCT(TotalSalary,(PaygroupCode=B2)*ISNUMBER(MATCH(Nationality,Nsubset,0)))

=SUMPRODUCT(SUMIFS(TotalSalary,PaygroupCode,B2,Nationality,Nsubset))

​