Forum Discussion
Sum the Values in Table B IF Table A Meets Criteria
I have 3 tables. One is a table with employee IDs (Table 'ID'). One is a table with employee IDs and their 2017 sales for one business (Table 'A'). The third table has employee IDs and their 2017 sal...
Ashish_Mathur
Super User
Super UserHi,
Based on the data that you have shared, what exact result are you expecting?
| Employee ID | Month | A Sales | B Sales | Company |
| 1 | 1 | 10 | 0 | A |
| 1 | 2 | 20 | 0 | A |
| 1 | 3 | 10 | 0 | A |
| 2 | 2 | 5 | 0 | A |
| 3 | 6 | 20 | 0 | A |
| 3 | 9 | 25 | 0 | A |
| 4 | 2 | 10 | 0 | A |
| 4 | 7 | 10 | 0 | A |
| 4 | 9 | 10 | 0 | A |
| 4 | 10 | 5 | 0 | A |
| 5 | 2 | 10 | 0 | A |
| 5 | 3 | 30 | 0 | A |
| 5 | 6 | 25 | 0 | A |
| 3 | 2 | 0 | 10 | B |
| 3 | 6 | 0 | 20 | B |
| 4 | 7 | 0 | 10 | B |
| 9 | 6 | 0 | 15 | B |
This data makes it easier to explain. I would expect the grand total to be 40 (which is B sales, except for employee 9 which doesn't have A sales >0). The problem is that for the Grand Total it sees that Total A Sales are >0 so it returns 55 (which is all of B sales). I need the grand total to be 40 since only employees 3 and 4 have A Sales >0.
My employee table:
Employee ID 1 2 3 4 5 9