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 |2 Answers
Reset to default 0Suggestion: 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) ''")
byrow
to applysumifs
to each unique date to avoid manually adding dates? – PatrickdC Commented Jan 17 at 21:04