0

I want to sum the amounts column B that have a receipt date in column A in 2018. I tried using this SUMIF formula for that:

=SUMIF(A2:A4,YEAR(2018),B2:B4)

I expected the result to be Fr. 5 but Google Sheet returns 0. What am I doing wrong?

1 Receipt Date Amount
2 03.05.2018 Fr. 2
3 07.10.2018 Fr. 3
4 09.02.2019 Fr. 5

(here's a link to this example table in Google Sheets)

Additional info:

  • Column A is formatted as date and column B as currency
  • The date format in Switzerland is dd.MM.yyyy
  • Google Sheet uses Fr. as a currency symbol for the Swiss Franc, the currency used in Switzerland.

1 Answer 1

1

There are many ways to do what you want:

  • the easiest is to create a new column with =YEAR(B1), and then SUMIF using this new column as the criteria

  • Alternatively, if you want to have single formula, you can use the following:

=SUM(index(filter(a1:b3,year(a1:a3)=2018),0,2))

  • If you want to use SUMIF, you can try the following:

=sumifs(B1:B3,A1:A3,">=1-jan-2018",A1:A3,"<=31-dec-2018")

9
  • Hi Oren, and thanks for your suggestions! Use the link to the example table in Google Sheets to see them in action. The total with the first suggestion is 0 and the result with the second suggestion is 43383. Unluckily none of them yielded the correct result.
    – Javier
    Apr 19 at 7:57
  • In your second suggestion, the FILTER seems to work correctly, but I don't know what goes wrong with INDEX as it returns 43380 and 3, which SUM then correctly adds up to 43383.
    – Javier
    Apr 19 at 9:09
  • 1
    In the first example, the filter parameter should be 2018, not YEAR(2018)
    – Oren
    Apr 19 at 9:12
  • On the second one, it should be a typo..
    – Oren
    Apr 19 at 9:14
  • 1
    I fixed the 2nd solution in the answer
    – Oren
    Apr 19 at 9:31

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.