CODEWITHSUNDEEP
google-sheetsformulas
Felix
Felix

Reputation: 11

Google Spreadsheets Formula to sum value if date falls in month and year

There a so many topics about Excel and how to Sum Value if date falls in particular month, but none of them helped me out with google spreadsheets.

Tryed out Excel "kind of formulars" but you can't use them in google spreadsheets:

=SUMIF(A2:A6,"MONTH(A2:A6)=1",B2:B6)
=SUMPRODUCT((MONTH($A$1:$A$10)=E1)+0;$B$1:$B$10)

Because there you can't get an array with =MONTH(A2:A5).

You can use it as =Month(A2) (A2 = "01.09.2017") and you get 1

Example

I want the value from the "Amount Column" from month june & year 2016

Date        Amount

01.05.2016   20,00€
24.05.2016   25,00€
03.06.2016   10,00€ 
13.06.2016   10,00€
30.06.2016   10,00€
05.01.2017   50,00€

Manually it would be SUM(B4:B6) to get 30,00€ & SUM(B7) to get 50,00€ for year 2017

What can I do to solve this problems? Thank you

Upvotes: 0

Views: 10034

Answers (1)

EduFlip.net
EduFlip.net

Reputation: 11

I found the easiest way to do this, was by creating an extra area on my spreadsheet to define my searches and use these in an ArrayFormula function.

Using =ArrayFormula(sumifs(B$2:B,month(A$2:A),G4,year(A$2:A),F4))

I reference the Year in Cell G4 and the month in F4.

For the best example of a use case you can look at the shared file.

You can find the example of my solution here: https://docs.google.com/spreadsheets/d/11_GCJBr2BZ2zFBDJj8I6iizD08y6fEmwZiPQjshNa7w/edit?usp=sharing It's not perfect, but I hope it helps you!

Upvotes: 1

Related Questions