Spreadsheet: 796473d1663305461-sumproduct-with-multiple-criteria-how-to-exclude-if-does-not-meet-criteria-forum-post-question-hd
Sheet: Budget (2)

[NAME] | Grant Month | Month | Grant Budget Category | Starting Amount | Planned Balance | Actual Balance | Planned Spend | Actual Spend | What I actually want to see
Grant A | 1 | 2022-03-01 00:00:00 | Administration | 10000 | 9166.666666666666 | 9100 | 833.3333333333334 |  | 800 |  | I need to calculate the [Actual Spend] during that month, when I am only given the Balance amounts
Grant A | 2 | 2022-04-01 00:00:00 | Administration |  | 8333.333333333332 | 8300 | 833.3333333333334 |  | 0 |  | for each month.  Essentially, I need to calculate as follows.
Grant A | 1 | 2022-03-01 00:00:00 | Operations - Staff S/B | 40000 | 36666.66666666666 | 36000 | 3333.333333333333 |  | 6000
Grant A | 2 | 2022-04-01 00:00:00 | Operations - Staff S/B |  | 33333.33333333333 | 30000 | 3333.333333333333 |  | 0 |  | [Actual Spend] = [Actual Balance] from this month  --  [Actual Balance] from the following month
Grant A | 3 | 2022-05-01 00:00:00 | Operations - Staff S/B |  |  |  |  |  | 0
Grant A | 1 | 2022-03-01 00:00:00 | Rental Assistance | 120000 | 110000 | 110000 | 10000 |  | 9000 |  | The reason I cannot simply use the above formula in column I is because, the Actual Balance
Grant A | 2 | 2022-04-01 00:00:00 | Rental Assistance |  | 100000 | 101000 | 10000 |  | 0 |  | numbers are not provided to me and may be added on separate lines later.  I thought a Sumproduct would
Grant B | 1 | 2022-03-01 00:00:00 | Operations - Staff S/B | 35000 | 32083.33333333333 | 32000 | 2916.666666666667 |  | 4000 |  | summaries all of the items for the same month, grant, and budget category.
Grant B | 2 | 2022-04-01 00:00:00 | Operations - Staff S/B |  | 29166.66666666666 | 28000 | 2916.666666666667 |  | 0
Grant B | 1 | 2022-03-01 00:00:00 | Cleaning & Maintenance | 2500 | 2291.666666666667 | 2200 | 208.3333333333333 |  | 200 |  | What I have come up with works, but only works if there is a following month in the data.  If there is
Grant B | 2 | 2022-04-01 00:00:00 | Cleaning & Maintenance |  | 2083.333333333333 | 2000 | 208.3333333333333 |  | 200 |  | not a following month in the data, then it lists the [Actual Balance] is listed from this month.  I guess
Grant B | 3 | 2022-05-01 00:00:00 | Cleaning & Maintenance |  | 1875 | 1800 | 208.3333333333333 |  | 150 |  | it is possibly attributing a zero amount to the following month, and thus, the formula works.
Grant B | 4 | 2022-06-01 00:00:00 | Cleaning & Maintenance |  | 1666.666666666667 | 1650 | 208.3333333333333 |  | 450
Grant B | 5 | 2022-07-01 00:00:00 | Cleaning & Maintenance |  | 1458.333333333333 | 1200 | 208.3333333333333 |  | 400 |  | What I really want is a way to exclude situations in which there is no following month in the data yet,
Grant B | 6 | 2022-08-01 00:00:00 | Cleaning & Maintenance |  | 1250 | 800 | 208.3333333333333 |  | 0 |  | or it is blank because it hasn't been entered yet.  Is there a way to do this?
Grant B | 1 | 2022-03-01 00:00:00 | Repairs | 30000 | 27500 | 27500 | 2500 |  | 2400
Grant B | 2 | 2022-04-01 00:00:00 | Repairs |  | 25000 | 25100 | 2500 |  | 3600 |  | Here is my current formula:
Grant B | 3 | 2022-05-01 00:00:00 | Repairs |  | 22500 | 21500 | 2500 |  | 2500 |  | =SUMPRODUCT(--([Grant Name]=[@[Grant Name]])*([Grant Budget Category]=[@[Grant Budget Category]])*([Month]=[@Month]),[Actual Balance])-SUMPRODUCT(--([Grant Name]=[@[Grant Name]])*([Grant Budget Category]=[@[Grant Budget Category]])*([Month]=(EDATE([@Month],1))),[Actual Balance])
Grant B | 4 | 2022-06-01 00:00:00 | Repairs |  | 20000 |  | 2500 |  | 3000
Grant B | 5 | 2022-07-01 00:00:00 | Repairs |  | 17500 |  | 2500 |  | 1500
Grant B | 6 | 2022-08-01 00:00:00 | Repairs |  | 15000 |  | 2500 |  | 0
Grant B | 1 | 2022-03-01 00:00:00 | Security Deposits | 18000 | 16500 | 16000 | 1500 |  | 500
Grant B | 2 | 2022-04-01 00:00:00 | Security Deposits |  | 15000 | 15500 | 1500 |  | 3500
Grant B | 3 | 2022-05-01 00:00:00 | Security Deposits |  | 13500 | 12000 | 1500 |  | 2000
Grant B | 4 | 2022-06-01 00:00:00 | Security Deposits |  | 12000 | 10000 | 1500 |  | 1000
Grant B | 5 | 2022-07-01 00:00:00 | Security Deposits |  | 10500 | 9000 | 1500 |  | 2000
Grant B | 6 | 2022-08-01 00:00:00 | Security Deposits |  | 9000 | 7000 | 1500 |  | 0
Grant C | 1 | 2022-07-22 00:00:00 | Administration | 20000 | 18333.33333333333 | 18000 | 1666.666666666667 |  | 2000
Grant C | 2 | 2022-08-22 00:00:00 | Administration |  | 16666.66666666666 | 16000 | 1666.666666666667 |  | 0
Grant C | 1 | 2022-07-22 00:00:00 | Operations - Staff S/B | 365000 | 334583.3333333333 | 300000 | 30416.66666666667 |  | 2000
Grant C | 2 | 2022-08-22 00:00:00 | Operations - Staff S/B |  | 304166.6666666666 | 298000 | 30416.66666666667 |  | 0
Grant C | 1 | 2022-07-22 00:00:00 | Rental Assistance | 110000 | 100833.3333333333 | 100000 | 9166.666666666666 |  | 5000
Grant C | 2 | 2022-08-22 00:00:00 | Rental Assistance |  | 91666.66666666666 | 95000 | 9166.666666666666 |  | 0
Grant B | 4 | 2022-06-01 00:00:00 | Repairs |  |  | 19000 |  |  | 3000
Grant B | 5 | 2022-07-01 00:00:00 | Repairs |  |  | 16000 |  |  | 1500
Grant B | 6 | 2022-08-01 00:00:00 | Repairs |  |  | 14500 |  |  | 0