1

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))

Link:
https://docs.google.com/spreadsheets/d/1NCzOmpJpnorB5diPCYewjU9c4KS_quMU9fUzyfGiJ8s/edit?usp=sharing

Improve this question
3
  • 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, 2019 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.
    – marikamitsos
    Sep 12, 2019 at 18:25
  • It wasn't deleted by me
    – Jack Salvatore
    Sep 12, 2019 at 19:48

1 Answer 1

Reset to default
1

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

Improve this answer
5
  • 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.
    – Jack Salvatore
    Sep 12, 2019 at 15:18
  • When dealing with date and date format there's always a problem :)
    – pjmg
    Sep 12, 2019 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.
    – Jack Salvatore
    Sep 12, 2019 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, 2019 at 21:27
  • Yes, that's exactly what I meant and it works perfectly, thanks a lot.
    – Jack Salvatore
    Sep 13, 2019 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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