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

Question

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.

Answer 1

Your spreadsheet has a separate sheet for each student, which makes working with the data harder rather than easier. You may want to move all the data into one sheet and use a row-oriented data layout, like this:

Name	Subject	Quarter	Score
Marie Curie	Chemistry	Q1	100
Albert Einstein	Chemistry	Q1	34
Marie Curie	Math	Q1	88
Albert Einstein	Math	Q1	66

This loosely corresponds with second normal form.

The primary benefit of a row-oriented data layout that it makes it easy to use spreadsheet functions like query() and filter() to further process the data and create reports.

Answer 2

Basic Formula

Uses the SUM function on an array returned by FILTER. Then IFERROR is added to handle errors generated by FILTER when no dates match.

=IFERROR(SUM(FILTER(
   cass!F:F, MONTH(cass!E:E)=3)), 0)

• IFERROR(value, [value_if_error])
• SUM(value1, [value2, …])
• FILTER(range, condition1, [condition2, …])

Advanced Formula

A single formula can be used to perform the same calculations on different sets of data. To do that, it is necessary to generalize (abstract) the formula by replacing specific values that will change with named variables (placeholders). This process creates a formula that can be placed in a LAMBDA function.

=BYCOL(B1:E1, LAMBDA(n, BYROW(A2:A13, LAMBDA(d,
   IFERROR(SUM(FILTER(
     INDIRECT(n & "!F:F"),
     MONTH(INDIRECT(n & "!E:E"))=MONTH(d))),0)))))

Steps from Basic Formula to Advanced

01:   INDIRECT References

A formula is needed to generate the two required range references, for example cass!E:E and cass!F:F.

Range references can be built from text strings using the INDIRECT function as shown below:

	A	B	C	D
1	abby	berlin	cass	dylan
2	=INDIRECT(A1 & "!F:F") =abby!F:F	=INDIRECT(B1 & "!F:F") =berlin!F:F	=INDIRECT(C1 & "!F:F") =cass!F:F	=INDIRECT(D1 & "!F:F") =dylan!F:F
3	=INDIRECT(A1 & "!E:E") =abby!E:E	=INDIRECT(B1 & "!E:E") =berlin!E:E	=INDIRECT(C1 & "!E:E") =cass!E:E	=INDIRECT(D1 & "!E:E") =dylan!E:E

The six different formulas in A2:D3 above can be reduced to just two by using n to represent the sheet name cell reference:

1. INDIRECT(n & "!F:F")   ~ 1st Range
2. INDIRECT(n & "!E:E")   ~ 2nd Range

02:   Lookup Month

The lookup month integer, for example 3 if the month is March, can be returned by applying the MONTH function to a date value as shown below:

	A	B
1	Jan 2021	=MONTH(A1) =1
2	Feb 2021	=MONTH(A2) =2
3-11	''      ''	''      ''
12	Dec 2021	=MONTH(A12) =12

Replacing the cell reference for the dates by d enables generalizing the lookup month integer as MONTH(d)

03:   Generalizing the Basic Formula

Now, the Basic Formula,

=IFERROR(SUM(FILTER(
   cass!F:F,
   MONTH(cass!E:E)=3)), 0)

can be generalized to:

IFERROR(SUM(FILTER(
  INDIRECT(n & "!F:F"), 
  MONTH(INDIRECT(n & "!E:E"))=MONTH(d))), 0)

04:   Passing in the Values

Now that the formula has been generalized the values for the sheet names n and the lookup date d need to be passed into the formula for it to work.

Two related functions, BYROW (row-by-row) and BYCOL (column-by-column), are used that enable passing a range (or array) into a LAMBDA function.

The LAMBDA function uses a variable to represent the current row or column in its formula. By already generalizing the formula with variables, all that remains is to pass in the ranges and name the variables correctly in the LAMBDA function.

     i.   Lookup Month

BYROW is used to pass the range of lookup dates A2:A13 into the LAMBDA which assigns them the variable name d

BYROW(A2:A13, LAMBDA(d,
  IFERROR(SUM(FILTER(
    INDIRECT(n & "!F:F"),
    MONTH(INDIRECT(n & "!E:E"))=MONTH(d))),0)

     ii.   Sheet Name

BYCOL is used to pass the range of sheet names B1:E1 into the LAMBDA which assigns them the variable name n

The formula is now complete

=BYCOL(B1:E1, LAMBDA(n, BYROW(A2:A13, LAMBDA(d,
   IFERROR(SUM(FILTER(
     INDIRECT(n & "!F:F"),
     MONTH(INDIRECT(n & "!E:E"))=MONTH(d))),0)))))