#### Bephart

##### New Member
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.

=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>

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
If you want to keep it matematical change out the " " with 0.
" " = a space caracter. EX. Solve this equation. 9 + "space character"
What you need is: 9+0

Hope that helps

Thank you so much for the reply.

I may be using the IFERROR function wrong. My problem is that The Monthly Sheet that contain a certain Sales Person won't sum because he does not appear
on all of the Monthly sheets. Is there a way to get JASON'S value returned for the month's that he was here without going back and entering him on every sheet ?

Thanks again

Also you will want to separate the iserror into 2 different iserror statements resulting in zero if error.
right now the whole statement will return error if either condition is an error, so when you separate them it will work for the ones that have only one month in error.

Replies
3
Views
319
Replies
0
Views
295
Replies
4
Views
189
Replies
1
Views
210
Replies
5
Views
176

1,196,481
Messages
6,015,451
Members
441,896
Latest member
clomah

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back