Compiling a y-t-d sales spreadsheet from monthly spreadsheets

[Bephart]

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))," ")

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

 

[easy2understandexcel]

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

 

[Bephart]

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

 

[easy2understandexcel]

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.