Sumif transaction date falls into one Month of one Financial Year

[Queeza]

Hello
I have a table of transactions, (ReflectReport), with multiple criteria columns. I have added a table column, (Fin Year) using
=IF(AND(MONTH([@Date])=MONTH(TODAY()),YEAR([@Date])=YEAR(TODAY())),"2","1")
1 equals last financial year and 2 equals this financial year.
Other columns, but not all, are:

Date	Accomplishment Cost	RM	Month	Fin Year Month	Calendar Month	Fin Year
03/Jul/2021​	1,569.00​		1	Jul	7	2
03/Jul/2020​	842.50​		1	Jul	7	1
06/Jul/2020​	1,569.00​		1	Jul	7	1
06/Jul/2020​	842.50​		1	Jul	7	1

Month uses: =IF([@Date]="","",CHOOSE(MONTH([@Date]),7,8,9,10,11,12,1,2,3,4,5,6))
Fin Year Month uses: =IF([@Date]="","",TEXT([@Date],"mmm"))
Calendar Month uses: =IF([@Date]="","",MONTH([@Date]))

I have another table, (TblActivity) with a column, "Spent This Month", that calculates spent this month by activity and schedule using:

=IFERROR(SUMIFS(ReflectReport[Accomplishment Cost],ReflectReport[Schedule],[@Schedule],ReflectReport[[Calendar Month ]],MONTH(TODAY()),ReflectReport[Activity],[@Activity]),"Check Dates")

I need this formula to use column "Date" to differentiate between the months of financial years, (and all the other criteria).
At the moment it is adding July 2020 and 2021 together.

Any help would be very much appreciated.

Thanks
Steve

 

[Dermot]

For a very simple solution, I would add a helper column in your transaction table that stores both month and year in a single number
= Month(date) + Year(date)*12
so 03/Jul/2020 would be 7 + 2020*12 = 24247
Then your SUMIFS can look up the month for today with Month(Today()) + Year(Today())

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IFERROR(SUMIFS(ReflectReport[Accomplishment Cost],ReflectReport[Schedule],[@Schedule],ReflectReport[Date],">"&EOMONTH(TODAY(),-1),ReflectReport[Date],"<="&EOMONTH(TODAY(),0),ReflectReport[Activity],[@Activity]),"Check Dates")

 

[Queeza]

For a very simple solution, I would add a helper column in your transaction table that stores both month and year in a single number
= Month(date) + Year(date)*12
so 03/Jul/2020 would be 7 + 2020*12 = 24247
Then your SUMIFS can look up the month for today with Month(Today()) + Year(Today())

Thank you
I had to add the *12 to the year in the SUMIF and then it worked perfectly.