I have workbook of income received from clients. Each worksheet is a separate client and the last sheet is YTD summary. I am trying to create a formula in YTD worksheet that sums the weekly totals from all the clients worksheet.(See attached)
I have created named tables for each client's worksheet and each column has named ranges to help create formulas. For each worksheet I also created a column for weeknum referencing from the date so I can then have their weekly total. I now want to take the results of all of the weekly and monthly totals and sum them into the YTD summary table.
Some clients, there will be several entries a week other clients there will be weeks between jobs so each row is not necessarily the next week.
As there already is a sumif formula in the cells that have the clients weekly total it makes it hard to work out how to write the new formula for YTD weekly total.
I don't want to use grouped data or pivot tables I just want a formula for the YTD summary. I guess similar to the formulas in weekly and monthly total for each client. Any help with this formula would be appreciated.

I am thinking that the formula would be something like this: but I know that this is wrong, please help


IF FORMULA
Logical Test:EFY_SUMMARY[[#This Row],[WEEK NUM]]<>E3
If Value is TRUE: SUMIF(E:E,LISA_INCOME[[#This Row],[WEEK NUM]],B:B)
AND
If value is TRUE: (E:E,JANEEN_INCOME[[#This Row],[WEEK NUM]],B:B)
If Value is FALSE: ""

Attached Files

2016FY CLIENT INCOME TRIAL.xlsx (28.6 KB, 16 views)