Sumifs formula with dynamic date range is not working in google sheet

0
votes

Here O column is my sum range, P2 = Date i.e. 2019-05-02

I want to sum all O column range on the bases of the last 30 days. i.e. p2-30.

This formula is working fine in Excel but not working in Google Sheets.

=SUMIFS(O:O,C:C,C2,P:P,"<"&TEXT(P2-30,"yyyy-mm-dd"))

I am really new to this and would appreciate any hints.....

2
google-sheetsgoogle-sheets-formulagoogle-sheets-query

2 Answers

0
votes

You might want to give the QUERY function a try.

In

F1:  =text(today()-30;"yyyy-mm-dd")
H1:  =query(O1:P18;"Select sum(O) where P> date '"&F1&"'")

In case you don't want a header

H1: =query(O1:P18;"Select sum(O) where P> date '"&F1&"' label sum(O) ''")

QUERY is a very nice function. Using a date can be a bit tricky, look for examples at https://www.benlcollins.com/spreadsheets/query-dates/

0
votes
=SUMPRODUCT(QUERY(O1:P, "select O 
                         where P <= date '"&TEXT(C2, "yyyy-MM-dd")&"'
                           and P >= date '"&TEXT(C2-30, "yyyy-MM-dd")&"'", 0))

0