I use Google Sheets to capture revenue and expenses. I have a separate file for each month and a master file to summarize each month.
The monthly account file contains a few sheets – each sheet contains revenue from the execution of a service, revenue from the sale of a product, expenses and the like.
The service sheet looks like this:
Now I want to be able to summarize the revenue from each such sheet by date. The easiest and only way I can do that is to use = SUMIF Formula for summing amounts from the column "C" by entering an entire date from the column "A" as a criterion, as follows:
It does the job, but the thing is that each month I would have to manually change the date (month and year specific) in the formula, and that's one thing I want to avoid.
I tried to work around this problem by changing the formula:
However, I've found that only the amount from column "C" is summed up when the date in column "A" ends with a specific day. In this case this is "03". However, the sum is always "0". That's why this solution does not work for me.
Is there a way to work around this problem by using the = SUMIF changed formula?
Another solution that I thought about is to put all the data into separate cells and use each cell as a criterion = SUMIF Formula, but for this to work, I would need a formula that automatically changes the month and year accordingly in those reference dates, unless such a formula can be placed inside = SUMIF even.
I feel like it's a very cumbersome way to achieve what I'm trying to do, but I definitely hope that an example of what I mean will help – say the fourth column in the picture below "D" and I mean use it as the range for a criterion to be met in the formula. To calculate the amount earned on 03.09.2019, I would use such a formula (where D192 is D192) 2019-09-03):
But again – for this to work, I would have to find a way to automatically change dates in column "D" with each new file, as it is very tedious to repeat them every month.
I hope I explained everything comprehensibly. I have a feeling that an array formula might be a sought-after solution, but every time I try to involve it when using Google Sheets, I do not get the results I'm looking for, and I'm afraid I do not quite understand how it works.
I started learning formulas a few months ago, when it turned out that I needed a neater account. Therefore treat me as a beginner and try to make your explanations as simple as possible. 😅