Sumifs not calculating correctly

Question

I have two tabs, one where I have dates and a number associated with the date. Another where it is broke down by month with the month's numbers. I am using this Sumifs to get the total for the month.

=SUM(
sumifs('K-8 Visit Dates & Impacts'!$C$3:$C,'K-8 Visit Dates & Impacts'!$B$3:$B,">="&date(2020,2,1),'K-8 Visit Dates & Impacts'!$B$3:$B,"<="&date(2020,2,28)),
sumifs('K-8 Visit Dates & Impacts'!$E$3:$E,'K-8 Visit Dates & Impacts'!$D$3:$D,">="&date(2020,2,1),'K-8 Visit Dates & Impacts'!$D$3:$D,"<="&date(2020,2,28)),
sumifs('K-8 Visit Dates & Impacts'!$G$3:$G,'K-8 Visit Dates & Impacts'!$F$3:$F,">="&date(2020,2,1),'K-8 Visit Dates & Impacts'!$F$3:$F,"<="&date(2020,2,28)),
sumifs('K-8 Visit Dates & Impacts'!$I$3:$I,'K-8 Visit Dates & Impacts'!$H$3:$H,">="&date(2020,2,1),'K-8 Visit Dates & Impacts'!$H$3:$H,"<="&date(2020,2,28)),
sumifs('K-8 Visit Dates & Impacts'!$I$3:$I,'K-8 Visit Dates & Impacts'!$J$3:$J,">="&date(2020,2,1),'K-8 Visit Dates & Impacts'!$J$3:$J,"<="&date(2020,2,28)),
sumifs('K-8 Visit Dates & Impacts'!$I$3:$I,'K-8 Visit Dates & Impacts'!$L$3:$L,">="&date(2020,2,1),'K-8 Visit Dates & Impacts'!$L$3:$L,"<="&date(2020,2,28)),sumifs('Misc Dates & Impacts'!$C$3:$C,'Misc Dates & Impacts'!$B$3:$B,">="&date(2020,2,1),'Misc Dates & Impacts'!$B$3:$B,"<="&date(2020,2,28))
)

But the dates are showing up in the correct month and in the following month. Can anyone help me figure out why this is?

Google Sheet Link: https://docs.google.com/spreadsheets/d/1ppPDe4LSzzR7pUS9VQTLqQ8cB5456Decpa6OGRaQDHE/edit?usp=sharing

Answer 1

paste in B2 and drag down:

=ARRAYFORMULA(SUM(
 IFNA(SUMIF(MONTH('K-8 Visit Dates & Impacts'!$B$3:$B), MONTH(A3&1), 'K-8 Visit Dates & Impacts'!$C$3:$C), 0),
 IFNA(SUMIF(MONTH('K-8 Visit Dates & Impacts'!$D$3:$D), MONTH(A3&1), 'K-8 Visit Dates & Impacts'!$E$3:$E), 0),
 IFNA(SUMIF(MONTH('K-8 Visit Dates & Impacts'!$F$3:$F), MONTH(A3&1), 'K-8 Visit Dates & Impacts'!$G$3:$G), 0),
 IFNA(SUMIF(MONTH('K-8 Visit Dates & Impacts'!$H$3:$H), MONTH(A3&1), 'K-8 Visit Dates & Impacts'!$I$3:$I), 0),
 IFNA(SUMIF(MONTH('K-8 Visit Dates & Impacts'!$J$3:$J), MONTH(A3&1), 'K-8 Visit Dates & Impacts'!$K$3:$K), 0),
 IFNA(SUMIF(MONTH('K-8 Visit Dates & Impacts'!$L$3:$L), MONTH(A3&1), 'K-8 Visit Dates & Impacts'!$M$3:$M), 0),
 IFNA(SUMIF(MONTH('Misc Dates & Impacts'!$B$3:$B),      MONTH(A3&1), 'Misc Dates & Impacts'!$C$3:$C), 0)))

Answer 2

This is not an exact answer to your question, but i think it might be a better way to be able to build what you need going forward. If you find a new tab on your shared sheet called MK.Help, you'll see this single formula in cell B1:

=ARRAYFORMULA(QUERY({IFERROR(QUERY(1*TEXT({'K-8 Visit Dates & Impacts'!B3:C;'K-8 Visit Dates & Impacts'!D3:E;'K-8 Visit Dates & Impacts'!F3:G;'K-8 Visit Dates & Impacts'!H3:I;'K-8 Visit Dates & Impacts'!J3:K;'K-8 Visit Dates & Impacts'!L3:M},{"mm-yyyy","0"}),"select 'K-8',Col1,Col2 where Col2>0 label 'K-8'''"),{"","",""});IFERROR(QUERY(1*TEXT({'CTE Visit Dates & Impacts'!B3:C;'CTE Visit Dates & Impacts'!D3:E;'CTE Visit Dates & Impacts'!F3:G;'CTE Visit Dates & Impacts'!H3:I;'CTE Visit Dates & Impacts'!J3:K;'CTE Visit Dates & Impacts'!L3:M},{"mm-yyyy","0"}),"select 'CTE',Col1,Col2 where Col2>0 label 'CTE'''"),{"","",""});IFERROR(QUERY(1*TEXT('District PD Dates & Impacts'!B2:C,{"mm-yyyy","0"}),"select 'District PD',Col1,Col2 where Col1>0 label 'District PD'''"),{"","",""});IFERROR(QUERY(1*TEXT('Misc Dates & Impacts'!B3:C,{"mm-yyyy","0"}),"select 'Misc Other',Col1,Col2 where Col2>0 label 'Misc Other'''"),{"","",""});IFERROR(QUERY(1*TEXT('Misc Dates & Impacts'!F3:G,{"mm-yyyy","0"}),"select 'Misc Summer',Col1,Col2 where Col2>0 label 'Misc Summer'''"),{"","",""});IFERROR(QUERY(1*TEXT('Misc Dates & Impacts'!J3:K,{"mm-yyyy","0"}),"select 'Misc District',Col1,Col2 where Col2>0 label 'Misc District'''"),{"","",""})},"select Col1,Col2,SUM(Col3) group by Col1,Col2 order by Col2,Col1 label Col1'Type',Col2'Month-Year',SUM(Col3)'Impacts'"))

That formula generates the entire table below it. I think you could use that going forward to build all your tables, and it might be a bit easier than all the shuffling between tabs. I was imagining you could leave it hidden as a sort of "back end aggregator" on your real sheet.