Is it necessary to use ArrayFormula to get the SUMIF function to work properly in Google Sheets?

I am trying to find the sum of values in a column based on the month in an adjacent column. For example, Sheet 1 has Three columns, one for student remark, one for payment date, and one for amount. There are three other sheets like this, one for each student. So, in Sheet 5, the totals of payments of all students are displayed month-wise.

I have used this formula to collect the totals from various sheets in Sheet5. In this example, the name of the first sheet is "cass". Payment date is stored in column E and payment amount in column F. I am trying to find the sum of payments in the month of April. The date is in the format DD/MM/YYYY (this format is set in the spreadsheet settings)

=SUMIF(cass!e3:e, month=4, cass!f3:f)

But the value getting returned for all of the sheets is 0. Before I learned about the =SUMIF() function, I tried making my own formula and it worked, but only for one of the sheets:

=sum(if(month(cass!E3:E)=3, cass!F3:F))

^ This formula is returning the total of the column F, regardless of what month it is. The same formula is still returning 0 for the other sheets.

In some other answers to questions about =SUMIF, I have seen users use the =ArrayFormula function, but I didn't think it was applicable to my case. What am I doing wrong in my formula?

Here's a duplicate of my original spreadsheet as a live example.

• Since you are working with multiple sheets then you need to work with smart way and yes ARRAY formula is required !! One easiest should like =SUMIFS(Sheet!F3:F10,Sheet!E3:E10,">="&H1,Sheet!E3:E10,"<="&H2) where H1 & H2 has date 01/04/2021 & 30/04/2021 ,,, and if you wanna single VALUE from all sheets then an ARRAY formula id required !! INDIRECT Function to reference a list of sheets in a SUMPRODUCT and SUMIFS not currently possible in Google Sheets.
– Rajesh S
Apr 30 '21 at 7:16