最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

SUMIF google sheets - automatically increment date - Stack Overflow

programmeradmin4浏览0评论

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!

Share Improve this question asked Jan 17 at 20:54 DavegullDavegull 11 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

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) ''")
发布评论

评论列表(0)

  1. 暂无评论