Thread: Sumif, index and match function

Dear all,

I have a time killing task. I have two files, one is data file (index and match function testing.xls) and another file is summary file. In data file, it includes the sales data of account managers. Account managers divided into two teams in the branch. However, some account managers work in the two team in a year, for example, account manager A work in team 1 for 3 months and then change to team 2.

Therefore, I want to get the sales data of specific account managers in two teams

I try to use index and match and sumif to get the data from data file. However, i try many times, it still not work.

I define the name of range in sheet " data by other branch" in datafile, the name is dataset.

1.Please teach me how can I use the formula to get the data from other file.
2. Second question, if it is possible, if there is no data of account manager in datafile, i hope to get the result of "" rather than #value.

Thanks

Hi

You cannot use SUMIF in the way that you are attempting.

Take a look at the books attached.
I renamed your first file as Source, and the data sheet as DataA to make the formulae for named ranges shorter to type and easier to read.

The named ranges I created were

Creditcard	=[Source.xls]DataA!$F$12:INDEX([Source.xls]DataA!$F$12:$F$98,COUNTA([Source.xls]DataA!$D$12:$D$98))				
LOC	=[Source.xls]DataA!$I$12:INDEX([Source.xls]DataA!$I$12:$I$98,COUNTA([Source.xls]DataA!$D$12:$D$98))				
RM	=[Source.xls]DataA!$D$12:INDEX([Source.xls]DataA!$D$12:$D$98,COUNTA([Source.xls]DataA!$D$12:$D$98))				
Team	=[Source.xls]DataA!$E$12:INDEX([Source.xls]DataA!$E$12:$E$98,COUNTA([Source.xls]DataA!$D$12:$D$98))		

and the formula then became simply
=SUMPRODUCT((RM=$B7)*(Team=$A7)*Creditcard)