Combine SUMIFS and INDEX MATCH - or an alternative that achieves the same...

[STUARTXL]

Hi All,

I'm trying to improve a variance report that I created a while ago. Please refer to the attached for the following example:

- Cells B2:D20 represent the my bank activity exported from my online account, into Excel.
- Column E is a column I use to denote the actual nature of the expense (you will see that the bank's description in Column B is not fit for my book-keeping purposes).
- Cells J1:R9 represent the variance report I set up which is used to inform my Head Office of income and expenditure activity on a weekly basis
- In the table J1:R9 there are columns headed 'Budget' - these figures are generated from a cashflow forecast sheet in my real document but for this example, just accept these figures as they are

Objective:
I want the 'Actual' columns in the variance report to generate the sum total of each type of expense for that week, based on the following conditions:
- The expense type listed in column J matches exactly the manual description type listed in column E
- The date the expense was incurred (as per the bank report) is greater than or equal to the week commencing date for that week and is less than the week commencing date of the next week

I thought of using some sort of combination of INDEX MATCH and SUMIFS formula where INDEX MATCH would be used to determine the expense description and the SUMIFS would be used to determine the dates criteria, but I have no idea on how to go about combining the two.

Any help appreciated. Thanks in advance.

[JohnTopley]

TRY

in L3

=SUMIFS($D$3:$D$20,$E$3:$E$20,[@[Exp. Type]],$B$3:$B$20,">="&$K$2,$B$3:$B$20,"<=" &$K$2+6)

in P3

=SUMIFS($D$3:$D$20,$E$3:$E$20,[@[Exp. Type]],$B$3:$B$20,">="&$O$2,$B$3:$B$20,"<=" &$O$2+6)

OR

=SUMIFS($D:$D,$E:$E,[@[Exp. Type]],$B:$B,">="&$K$2,$B:$B,"<=" &$K$2+6)

[STUARTXL]

Thanks John...again!