CODEWITHSUNDEEP
dategoogle-sheetssumifs
Davegull
Davegull

Reputation: 1

SUMIF google sheets - automatically increment date

I have a sheet where google form responses are sent. Each response has the date and then data on other columns which I'm looking to transfer to a summary sheet. I have a basic sumif formula to capture data in a separate summary sheet

I need to copy the formula across date columns in the summary sheet for each day of the year, but don't want to have to manually type in each date into the formula.

Here is the basic formula:

=sumifs('Brandon Form Responses'!$P$2:$P$503,'Brandon Form Responses'!$B$2:$B$503,"1/1/2025")

Column B = date Column P = revenue collected

Can anyone help me with this?

The SUMIF function doesn't automatically increment the dates - I'm sure there's a simple solution to this!

Upvotes: 0

Views: 83

Answers (2)

Harun24hr
Harun24hr

Reputation: 37125

QUERY() would be simpler one. You may try-

=QUERY('Brandon Form Responses'!B2:C,"select B, sum(C) where B is not null group by B label sum(C) ''")

Upvotes: 0

PatrickdC
PatrickdC

Reputation: 2496

Suggestion: Use UNIQUE to List All Dates and then BYROW to Distribute SUMIFS Formula

Try using:

=LET(a, UNIQUE(TOCOL('Brandon Form Responses'!B2:B,1)), 
     b, BYROW(a,
             LAMBDA(x,
                  SUMIFS('Brandon Form Responses'!P1:P,
                         'Brandon Form Responses'!B1:B,
                          x))),
     HSTACK(a,b))

I applied this to a sample data (since you have not provided one):

Date (Col B) Value (Col P)
1/1/25 1
1/1/25 2
1/2/25 3
1/3/25 4
1/5/25 5
1/1/25 6

With an output:
1/1/25 9
1/2/25 3
1/3/25 4
1/5/25 5

References:

Upvotes: 0

Related Questions