Hi
I am trying to compile a Year-To-Date Spreadsheet to show Sales Person, Units and Profit using Data from Monthly Spreadsheets. My goal is to total the above columns from the Monthly Sheets on a Year-To Date Sheet.
My problem is that some Sales People that are here now were not hereat the beginning of the year and others left before the year ended. I used INDEX/MATCH to add cells from Monthly sheets and got N/A returned for Sales People who did not appear on some sheets. I added the IFERROR function to get rid of the N/A, however the partial year Sales Person's figures still failed to Sum.
Below is a screen Shot and the formula I currently have for the unit column.
Thanks for any help you can give me.
=IFERROR(INDEX('Month 1'!B$4:B$9,MATCH($A4,'Month 1'!$A$4:$A$9,0))+INDEX('Month 2'!B$4:B$9,MATCH($A4,'Month 2'!$A$4:$A$9,0))," ")
<tbody>
</tbody>
I am trying to compile a Year-To-Date Spreadsheet to show Sales Person, Units and Profit using Data from Monthly Spreadsheets. My goal is to total the above columns from the Monthly Sheets on a Year-To Date Sheet.
My problem is that some Sales People that are here now were not hereat the beginning of the year and others left before the year ended. I used INDEX/MATCH to add cells from Monthly sheets and got N/A returned for Sales People who did not appear on some sheets. I added the IFERROR function to get rid of the N/A, however the partial year Sales Person's figures still failed to Sum.
Below is a screen Shot and the formula I currently have for the unit column.
Thanks for any help you can give me.
=IFERROR(INDEX('Month 1'!B$4:B$9,MATCH($A4,'Month 1'!$A$4:$A$9,0))+INDEX('Month 2'!B$4:B$9,MATCH($A4,'Month 2'!$A$4:$A$9,0))," ")
` | TOTALS | ||
SALES PERSON | UNITS | PROFIT | |
JOE | 10 | $10,800 | |
JACK | 12 | $12,000 | |
JASON | |||
BILL | |||
Bill was only here for Month 1 | |||
Jason was not here in Month 1but wasin Month 2 | |||
Joe and Jack were here both Months |
<tbody>
</tbody>