I am a beginner in Google Sheets and I'm working on a workbook with three sheets: Income, Expenditure, and Summary. My goal is to create a summary sheet that automatically calculates the total income and expenses for each month and displays the results in the corresponding rows.
Workbook Structure: Income Sheet: Contains columns for Date, Description, and Amount. Expenditure Sheet: Contains columns for Date, Description, and Amount. Summary Sheet: Contains columns for Month, Total Income, Total Expenses, and Net Profit/Loss.
What I Want to Achieve: In the Summary sheet, I want the Total Income column to automatically sum the amounts from the Income sheet based on the month and year. Similarly, I want the Total Expenses column to sum the amounts from the Expenditure sheet based on the month and year. The Net Profit/Loss should then be calculated as the difference between Total Income and Total Expenses for each month.
Formula Attempt: I attempted to use the SUMPRODUCT function to achieve this for the Total Income column. Here is the formula I tried:
=SUMPRODUCT((TEXT(Income!A:A,"mmmm")="January")*(Income!C:C))
However, I am receiving an error and I'm unsure how to resolve it.
Issues: Error Message: The formula throws an error and doesn’t return the expected results. Date Matching: I’m not sure if the date matching is being done correctly using the TEXT function within SUMPRODUCT.
Additional Information: The Date column in both Income and Expenditure sheets is in mm/dd/yyyy format. I want to ensure the formula dynamically updates for all months, not just January. Here is a link to a dummy template of my workbook for reference
What I Need: Assistance in correcting the formula or a better approach to achieve the desired result. Any suggestions on how to efficiently sum the income and expenses by month, ensuring the summary sheet updates automatically as data is added to the income and expenditure sheets.
Thank you in advance for your help!
Try to change your formulas to:
=SUMPRODUCT(TEXT(Income!A:A,"mmmm")=A2,Income!D:D)
=SUMPRODUCT(TEXT(Expenditure!A:A,"mmmm")=A2,Expenditure!D:D)