0

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.

1
  • 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 at 7:16
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy