# How to sum values between two dates?

I have a table made up of numbers followed by dates like the one below only with more columns (link below).

How would I sum only the values that occur in one specific year?
For example, in this case, how would I sum the the values that occur only in 2014?

``````    A              B             C           D             E           F
----------------------------------------------------------------------
\$200.000,00    12/9/11     \$100.000,00   4/4/12       \$50.000,00    6/12/12
\$25.000,00     4/15/14     \$32.500,00    11/26/14     \$32.500,00    4/10/15
\$50.000,00     9/29/17     \$50.000,00    9/1/18       \$50.000,00    5/11/18
``````

I created a sample spreadsheet in Google Sheets with my failed attempt

My idea was to make a formula like this:
`=SUMIFS((E4;G4),(F4;H4),">="&DATE(1/1/14),(F4;H4),"<="&DATE(1,1,15))`

• Welcome. While the linked spreadsheet is helpful, all the relevant details, like the formulas that you tried, should be included on the question itself. Please edit the question to include those formulas.
– Rubén
Sep 11 '19 at 23:14
• Please do NOT delete the referring data spreadsheet. It takes no space in your drive and is a reference point for future users as well. Sep 12 '19 at 18:25
• It wasn't deleted by me Sep 12 '19 at 19:48

To use `SumIf` you need a single column with data to be summed up, and a second column with something you can check (a boolean for example).

I extract every 2nd cell for column 1 :

``````={
filter(transpose(C4:AG4); mod(transpose(ArrayFormula(COLUMN((C4:AG4))+1));2)=0);
filter(transpose(C5:AG5); mod(transpose(ArrayFormula(COLUMN((C5:AG5))+1));2)=0);
filter(transpose(C6:AG6); mod(transpose(ArrayFormula(COLUMN((C6:AG6))+1));2)=0)
}
``````

And for column 2, the date :

``````={
filter(transpose(C4:AG4); mod(transpose(ArrayFormula(COLUMN((C4:AG4))));2)=0);
filter(transpose(C5:AG5); mod(transpose(ArrayFormula(COLUMN((C5:AG5))));2)=0);
filter(transpose(C6:AG6); mod(transpose(ArrayFormula(COLUMN((C6:AG6))));2)=0)
}
``````

Now I just need to check every date in column 2 with something like this : `=AND(YEAR(L22)>=2014;YEAR(L22)<2015)`

That's where I saw that there are problems with your date. Some of them are string : `=ISDATE()` return a falsy.

Please correct the date input. Anyway, here's the final `sumif` :

``````=sumif(M22:M63;true;K22:K63)
``````

`M22:M63` is column 1
`True` is our criteria (>=2014 AND <2015)
`K22:K63` is column 2 after boolean check

• Thank you so much, the bit that I didn't know and that led to getting an error was that you need to have your data in columns and you can't just select multiple cells. I've changed my format now so the dates and values have their own row and it works as well; however, your reasoning helped a lot. Sep 12 '19 at 15:18
• When dealing with date and date format there's always a problem :)
– pjmg
Sep 12 '19 at 15:35
• Yes for sure :). On that topic, do you know if there's a way to make the date automatically update each year. For example, if the parameters are from 1/1/2019 to today, when the year changes to 2020 I want the parameter to automatically update to 1/1/20. Sep 12 '19 at 19:51
• I'm not sure i've understand but is this `=date(year(today()),1,1)` what you need ? (if `today()` is in `2020`, then the date will always be `2020/01/01`. Right now it's `2019/01/01` because well `year(today()) = 2019`)
– pjmg
Sep 12 '19 at 21:27
• Yes, that's exactly what I meant and it works perfectly, thanks a lot. Sep 13 '19 at 0:22