sum across worksheets if contents of two columns meet criteria

Hi Everyone.

Can anyone look into the attached file and tell me how to sum across the whole workbook if column A and J of each worksheet meets criteria from "payments" sheet.
I want to check how much we got paid by the council on a date I'll enter manually, to see if all the payments were entered.
And how much I invoiced our clients on a date I'll also input myself.

Wish I knew this kind of formulas but whenever I try I get a headache as I look at formulas I never done before and try to "crack" the code to use for my own purposes.
Once one of you guys helped me create formula like this(which maybe helpful in this case):

=SUMIFS(INDIRECT(TEXT($D$1-7, "DD.MM")&"!O:O"),INDIRECT(TEXT($D$1-7, "DD.MM")&"!D:D"),D45,INDIRECT(TEXT($D$1-7, "DD.MM")&"!E:E"),E45)

for my other file.

I'm only starting my journey to learn Indirect and Match formulas, so no idea yet.. how to nest functions in it.
Any help greatly appreciated.

More details in the attached workbook.

Thank you NBVC will try that! That should give me amounts breakdown per name even, since names of the tabs correspond to my residents names.
Is there a way to sum only the amounts getting info from date client and amount columns across the tabs tho?

Actually what I need is to sum all the council payments from dates I enter in column B of my active sheet

so sum I across sheets if their column A says "council" and column J equals date I enter in B of my active sheet.
then another formula has to
sum all amounts across the worksheets if their column H equals date from column E of active sheet and column A says "client"

You're the best!!!!

but the thing is(now i figured), i'll be moving residents out of the file each time they left and cleared their balance so the sums of council wont be matching actual monies received or invoices from older dates, so guess have to skip this part . I'm happy tho that I'll be able to check how much i invoiced of lets say 13/08/2013 or how much i received from the council ( for income monitoring purposes )

I guess I'll have to limit myself to checking current invoices/payments then recording them manualy.

I don't have any ideas of how to do it so that it works despite me moving residents out of the file (to Residents "name of the care home" leavers file)

Thanks for your time NBVC reputation up and have a wonderful day!