Count IF with data on two sheets

[Rosechove]

Hello,

I am trying to figure out how much in sales each salesperson has generated by location. My issue is the sales information and location information are stored on two separate sheets.

Is there a formula I can use in Table A that will be able to sum the total purchase amounts by area?

(Table A - Summary)

I need to take the total cost from the Purchases sheet. The area is listed per account on the Accounts sheet.

(Table B - Purchases)

(Table C - Accounts)

Thank you in advance!

 

[Candyman8019]

Try the following in the summary tab B2:

=sumifs(Purchases!D2:D21,Purchases!C2:C21,B$1,Purchases!A2:A21,$A2)

This should work as long as you have all of your locations listed on the summary tab column A

oh, scratch that...I just noticed that purchases column A is not the location.

 

[Candyman8019]

possibly something like this.

=sumifs(Purchases!D2:D21,Purchases!C2:C21,B$1,xlookup($A2,Accounts!A2:A6,Accounts!C2:C6),$A2)

 

[Fluff]

Hi & Welcome to MrExcel.
How about

=SUM(FILTER(Purchases!$D$2:$D$30,(Purchases!$C$2:$C$30=B$1)*(ISNUMBER(MATCH(Purchases!$A$2:$A$30,FILTER(Accounts!$A$2:$A$30,Accounts!$C$2:$C$30=$A2),0)))))