3

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 at 5:14
2

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 at 8:57

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.