SUM IF - 2 Different tables

[jwgreen1986]

I am wanting to sum two totals if possible. I have a used sale section and a new sale section. These are both separate for other reasons and reports.
However i am trying to now create a new section that will combine both the new sales and used sales totals together.

I have a list of all employees. basically i want to search for their name in the new sales table, get how many sales they have, search for them in he used sales table, get how many sales they have from there and then add these together to give me an overall total.

It gets a little trickier because some of the staff will only be in the new sales table and some will only be in the used table and then there are some that are in both. so if someone isnt in one of the tables i dont want it to bring back an error, just bring back the total of the table they are in.

any ideas? i was thinking of using a vlookup but wouldnt know how to merge and add the two results in one formula.

 

[tyija1995]

Hey,

Could you not use this:
=SUMIF(A1:A5,"Employee1",B1:B5) + SUMIF(D1:D5,"Employee1",E1:E5)

Where A1:A5 is the list of employee names on table 1, B1:B5 is the used sales, D1:D5 is the list of employee names on table 2 and E1:E5 the new sales?

 

[jwgreen1986]

Hi, is the employee1 and 2 the names of the tables?

for easiness i have two ranges set up in excel. one called NT the other UT. each of these have 4 columns each. name, site, scheme, sales.
i then have a separate section with a list of all employees. i want to search for the employee in this separate sections in the NT range (NewTable) and get the sales number, i then want to search for this employee in the UT (used table) and get the sales value from here and then add these two together.

 

[jwgreen1986]

so i worked out what you meant and have it sorted. thank you very much

 

[tyija1995]

Hey,

Sorry I edited it so it both looks at Employee1 which is just a placeholder for an actual Employee's name - as I do not know your data I used "Employee1" instead :D

To get the sum for the NT just use =SUMIF(NewTable[name],"Your Employees name goes here",NewTable[sales])
To get the sum for the UT just use = SUMIF(UsedTable[name],"Your Employees name goes here",UsedTable[sales])

Add the two together to get the sum for both tables. You can hook the employees name up to a different cell which could then be a dropdown list of all available employee names for example.