I need help with this data I have created. This is a Table, so lets say "Table1":
<colgroup><col><col><col span="7"></colgroup><tbody>
</tbody>
I need to be able to return the value for a specific rep, on a specific date, by transaction. What I have so far is this:
=SUMIFS(Table1[Trans1],Table1[ReportDate],A2,Table1[EmployeeName],A1)
What I need to do is be able to make the formula work using "Trans1" as a variable so it can look at a cell such as A3 to find the correct column. So A1 is the name, A2 is the date, and A3 would be the transaction type it needs to find and return the value for.
Please help!!!
ReportDate | EmployeeName | Trans1 | Trans2 | Trans3 | Trans4 | Trans5 | Trans6 | Trans7 |
8/1/14 | Rep1 | |||||||
8/1/14 | Rep2 | |||||||
8/1/14 | Rep3 | 2 | 1 | |||||
8/1/14 | Rep4 | 2 | 1 | 2 | ||||
8/1/14 | Rep5 | |||||||
8/1/14 | Rep6 | |||||||
8/1/14 | Rep7 | |||||||
8/1/14 | Rep8 | |||||||
8/1/14 | Rep9 | 3 | ||||||
8/1/14 | Rep10 | |||||||
8/1/14 | Rep11 | |||||||
8/1/14 | Rep12 | 1 | 14 | 6 | 3 | |||
8/1/14 | Rep13 | 1 | 1 | |||||
8/1/14 | Rep14 | |||||||
8/1/14 | Rep15 | |||||||
8/1/14 | Rep16 | 4 | 4 | 4 | ||||
8/1/14 | Rep17 | 5 | 2 | |||||
8/1/14 | Rep18 | 1 | 6 | |||||
8/1/14 | Rep19 | |||||||
8/1/14 | Rep20 | 1 | ||||||
8/1/14 | Rep21 | 8 | ||||||
8/1/14 | Rep22 | |||||||
8/1/14 | Rep23 | |||||||
8/1/14 | Rep24 | |||||||
8/1/14 | Rep25 | |||||||
8/1/14 | Rep26 | 1 | 6 | 7 | 5 | |||
8/1/14 | Rep27 | 5 | ||||||
8/1/14 | Rep28 | 2 | ||||||
8/1/14 | Rep29 | 1 | 2 | 12 | 1 | |||
8/1/14 | Rep30 |
<colgroup><col><col><col span="7"></colgroup><tbody>
</tbody>
I need to be able to return the value for a specific rep, on a specific date, by transaction. What I have so far is this:
=SUMIFS(Table1[Trans1],Table1[ReportDate],A2,Table1[EmployeeName],A1)
What I need to do is be able to make the formula work using "Trans1" as a variable so it can look at a cell such as A3 to find the correct column. So A1 is the name, A2 is the date, and A3 would be the transaction type it needs to find and return the value for.
Please help!!!
Last edited: