# Rolling Year Formula

I have a Google sheet where I would like a score based on a rolling year figure.

I have managed to do these separately using the formulas:

``````=query(A2:B," where A >= date '"&TEXT(today()-E1,"yyyy-mm-dd")&"' and A <= date '"&TEXT(today(),"yyyy-mm-dd")&"' ")
``````

and

``````=(COUNTIF(H2:H15, "<>")*COUNTIF(H2:H15,"<>"))*(SUM(H2:H15))
``````

However, I was wondering if there was a way these two could be combined so I just get the score.

Here is a demo sheet.

• Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. – marikamitsos Nov 28 '19 at 5:14

You can replace both your formulas with this single one:

``=SUM(FILTER(B2:B,A2:A>(TODAY()-E1)-1))*POWER(COUNT(FILTER(B2:B,A2:A>(TODAY()-E1)-1)),2)``

Functions used:

### Pro tip

Use the following formula without the need of an extra cell. It also takes into account leap years.

``=SUM(FILTER(B2:B,A2:A>(TODAY()-(TODAY()-(EDATE(TODAY(),-12))))-1))*POWER(COUNT(FILTER(B2:B,A2:A>(TODAY()-(TODAY()-(EDATE(TODAY(),-12))))-1)),2)``

• Thank you very much! That is great! – user233395 Nov 28 '19 at 8:57