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.
Best Answer
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:
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.