SUMIF google sheets - automatically increment date - Stack Overflow

admin管理员组

文章数量:1321074

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!

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!

• date
• google-sheets
• sumifs

Share Improve this question Follow asked Jan 17 at 20:54 DavegullDavegull 111 bronze badge 4

• Welcome to StackOverFlow! Please provide a markdown table (you may create one with the help of this link ) or an anonymous sample spreadsheet (using this link). Also, include your initial output, and your expected output so that we can further help you. – leylou Commented Jan 17 at 20:57
• Why not just list all the unique dates and then use byrow to apply sumifs to each unique date to avoid manually adding dates? – PatrickdC Commented Jan 17 at 21:04
• Also, please provide sample input and expected output so that we may check if our output is correct. – PatrickdC Commented Jan 17 at 21:05
• Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented Jan 18 at 5:39

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

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:

• UNIQUE
• BYROW
• TOCOL
• HSTACK

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) ''")

本文标签： SUMIF google sheetsautomatically increment dateStack Overflow