I have attached worksheet that I am trying to extract summed data based on multiple criteria.
The worksheet has a list of categories (Column A) against which payments are due based on day of month (Column B formatted as ‘dd’) and month in financial year (Row 7 formatted as ‘mmm’)) when they are due with actual year formatted as ‘General’ in Row A3. As can be seen, some Categories may receive more than one payment in any one month.
I want to calculate cumulative payments made to each Category upto and including the date of calculation (using TODAY function in B1) by using following two criteria
- Month from Row 7
- Day of month from Column A
For example on 10 July 2022 the calculation should include cells C10:F16 for each specific Category; on 25 September it should include cells C10:H20 etc.
I have tried following SUMIFS with three criteria (Category, month, day of month) but get #Value error.
=(SUMIFS($C$10:$N$21,$A$10:$A$21,A27,$C$7:$N$7,"<="&MONTH(B$1),$B$10:$B$21,"<="&DAY(B$1)))
I have also tried SUMIF for each row and then further SUMIF by Category based of 'row' SUMIF values but this obviously only gives complete end of month totals, even if B1 is before month end.
Is this feasible using inbuilt functions or will it require complex VBA code; in which case Help!
