SUMIFS with a date requirement included.

Dene Marcello

I have a Disbursement tracker, showing Request Status, Date Requested, Amount and Project. I have another sheet, Project Plan showing the summation of Disbursements by Project. I am currently using the formula below to summarize all Amounts that are disbursed. I am using my request status column and including everything other than "Sent to Management for Approval". This works but I think it would be easier to use a NOT formula, so NOT = "Sent to Management for Approval" and NOTBLANK.

Also, I am getting into year two on my disbursement tracker so I need to add a requirement that the date requested is in the current year (2023). Can I add this to the formula?

In summary, It is the Disbursed column formula on the Project plan that I want to fix. I want it to look at the Disbursement Tracker, See if the Project Task = @row, is NOT blank or sent to management for approval and it is in year 2023, then to consider it disbursed for the project plan. Thank you!

Screen Shot 2022-12-07 at 1.49.15 PM.png

Screen Shot 2022-12-07 at 2.00.10 PM.png

=SUMIFS({Partner Disbursement Request Tracker Total Request}, {Partner Disbursement Request Tracker Project}, =[Task Name]@row, {Partner Disbursement Request Tracker Request Statu}, ="Complete") + SUMIFS({Partner Disbursement Request Tracker Total Request}, {Partner Disbursement Request Tracker Project}, =[Task Name]@row, {Partner Disbursement Request Tracker Request Statu}, ="Receipt requested from Partner") + SUMIFS({Partner Disbursement Request Tracker Total Request}, {Partner Disbursement Request Tracker Project}, =[Task Name]@row, {Partner Disbursement Request Tracker Request Statu}, ="Funds sent to Partner") + SUMIFS({Partner Disbursement Request Tracker Total Request}, {Partner Disbursement Request Tracker Project}, =[Task Name]@row, {Partner Disbursement Request Tracker Request Statu}, ="Requested Payment from Tenfold") + SUMIFS({Partner Disbursement Request Tracker Total Request}, {Partner Disbursement Request Tracker Project}, =[Task Name]@row, {Partner Disbursement Request Tracker Request Statu}, ="Request Payment from AMH-US")

Paul Newcome

Try something like this:

=SUMIFS({Partner Disbursement Request Tracker Total Request}, {Partner Disbursement Request Tracker Project}, =[Task Name]@row, {Partner Disbursement Request Tracker Request Statu}, AND(@cell <> "Sent to Management for Approval", @cell <> ""), {Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Kelly Moore

Hey @Dene Marcello

try this

=SUMIFS({Partner Disbursement Request Tracker Amount}, {Partner Disbursement Request Tracker Project}, =[Task Name]@row, {Partner Disbursement Request Tracker Request Statu}, AND(@cell <> "Sent to Management for Approval", @cell <> ""), {Partner Disbursement Request Tracker Date Requeste}, IFERROR(YEAR(@cell), 0)=2023)

Paul Newcome

Try something like this:

=SUMIFS({Partner Disbursement Request Tracker Total Request}, {Partner Disbursement Request Tracker Project}, =[Task Name]@row, {Partner Disbursement Request Tracker Request Statu}, AND(@cell <> "Sent to Management for Approval", @cell <> ""), {Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Dene Marcello

Paul, Thank you. Yes, this is working but it is picking up the expenses that relate to the year I am in. I would like to tell it to pick up expenses that are for 2023. Can I do that? and thank you for your help!

Dene Marcello

I tried this: =SUMIFS({Partner Disbursement Request Tracker Amount}, {Partner Disbursement Request Tracker Project}, =[Task Name]@row, {Partner Disbursement Request Tracker Request Statu}, AND(@cell <> "Sent to Management for Approval", @cell <> ""), {Partner Disbursement Request Tracker Date Requeste}, =(YEAR(2023))) but I am getting #invalid Date Type

Kelly Moore

Hey @Dene Marcello

try this

=SUMIFS({Partner Disbursement Request Tracker Amount}, {Partner Disbursement Request Tracker Project}, =[Task Name]@row, {Partner Disbursement Request Tracker Request Statu}, AND(@cell <> "Sent to Management for Approval", @cell <> ""), {Partner Disbursement Request Tracker Date Requeste}, IFERROR(YEAR(@cell), 0)=2023)

Paul Newcome

Apologies. I saw the "current year" in your original post and assumed that when you mentioned 2023 you were simply trying to plan ahead for the year change.

@Kelly Moore Thanks for grabbing that!