2

I am using Google Sheets to track income and expenses. I have a separate file for each month and a master file to summarize each month.

The file for monthly account contains a couple of sheets - each separate sheet including income from carrying out a service, income from selling a product, expenses and such.

Services sheet looks like this:

enter image description here

Now I want to be able to sum up the income from each such sheet by date. The easiest and the only way I know how to do so is by using =SUMIF formula to sum amounts from "C" column by inputting a whole date from column "A" as a criteria, like so:

=SUMIF(inservice!A3:A200;"2019-09-03";inservice!C3:C200)

It does the job but the thing is each month I would have to change the date (month and year specifically) in the formula manually and that's a thing I would like to avoid. I have tried going around this problem with changing the formula to:

=SUMIF(inservice!A3:A200;"*03";inservice!C3:C200)

I have though that by doing so it would only sum amount from the "C" column if date in the "A" column ended with a specific day - in this case it being "03", however the sum is always "0", therefore this solution does not work for me.

Is there a way to go around this problem by using the =SUMIF modified formula?

Another solution I was thinking of is to input all dates in separate cells and use each cell as a criteria for =SUMIF formula but for this to work I would need a formula that automatically changes month and year accordingly in these reference dates, unless such formula can be placed within =SUMIF itself.

I have a feeling it's a very roundabout way to achieve what I am trying to do, but in any case I hope an example of what I mean will help - let's say that the forth column in the picture below is called "D" and I use it as a range for a criteria to be met in the formula. In order to calculate amount earned on 2019-09-03 I would use such formula (where D192 equals 2019-09-03):

=SUMIF(inservice!A3:A200;inservice!D192;inservice!C3:C200)

enter image description here

But again - for this to work I would need to find a way for dates in "D" column to change with each new file automatically as it is very bothersome to redo it every single month.

I hope I have explained everything comprehensibly. I do have a feeling an array formula may be a solution sought, however every time I have tried to include it while using Google Sheets it does not give me desired results and I fear I do not have a good understanding of how it works.

I have started learning formulas since a couple of months ago when it became apparent I have needed more orderly calculated account, therefore treat me as a total beginner and please do try to make your explanations as simple as possible. 😅

  • What about =SUMIF(inservice!A3:A200;$E$1;inservice!C3:C200) where E1 is the date you need ? (I.E. 2019-09-03 here). You''ll have only 1 cell to modify each month. (and btw you can us CTRL + D to expand formulas) If you can provide a sample with your problem and desired result too, it'll be a way for us to better understand your problem. – pjmg Sep 7 at 18:44
-1

This formula sums all the sums in column C by date:

=sort(transpose(QUERY(inservice!A3:C200, "select SUM(C) where A is not null pivot A", 0)), 1, FALSE) 

Result:

Result given your example

You might want to check the QUERY function, which is what solves your problem. I used TRANSPOSE and SORT in order to keep your presentation style.

New contributor
Rayhunter is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Please briefly describe how this answer the question. – Rubén yesterday

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.