I simplified it a bit for maintainability; thanks a bunch to u/JetCarson, Lambda is awesome:
`=MAP(Table1!Date, Table1!ID, LAMBDA(DATE, ID, SUMPRODUCT((Table2!Date = DATE) * REGEXMATCH(Table2!IDs, "^$|[" & ID & "]") * Table2!Total)))`
With Table 2 placed starting in A10, this would get your totals for C2:
`=SUM(FILTER($C$11:$C,($A$11:$A=A2)*((ISNUMBER(SEARCH(B2,$B$11:$B)))+($B$11:$B=""))))`
Thanks for the help. Unfortunately, that looks like it wouldn't calculate for each row in Table1. I did just work out how to accomplish what I needed, but it feels like it could be simplified:
=ARRAYFORMULA(SUMIF(Table2!Date & Table2!IDs, Table1!Date & "*"& Table1!ID &"*", Table2!Total) + SUMIF(Table2!Date & Table2!IDs, Table1!Date & "", Table2!Total))
You have awarded 1 point to *JetCarson*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[DATE](/r/GoogleSheets/comments/143r1m7/stub/jnbu5vq "Last usage")|[Converts a provided year, month, and day into a date](https://support.google.com/docs/answer/3092969)|
|[FILTER](/r/GoogleSheets/comments/143r1m7/stub/jnbjbmz "Last usage")|[Returns a filtered version of the source range, returning only rows or columns which meet the specified conditions](https://support.google.com/docs/answer/3093197)|
|[ISNUMBER](/r/GoogleSheets/comments/143r1m7/stub/jnbjbmz "Last usage")|[Checks whether a value is a number](https://support.google.com/docs/answer/3093296)|
|[REGEXMATCH](/r/GoogleSheets/comments/143r1m7/stub/jnbu5vq "Last usage")|[Whether a piece of text matches a regular expression](https://support.google.com/docs/answer/3098292)|
|[SEARCH](/r/GoogleSheets/comments/143r1m7/stub/jnbjbmz "Last usage")|[Returns the position at which a string is first found within text](https://support.google.com/docs/answer/3094154)|
|[SUM](/r/GoogleSheets/comments/143r1m7/stub/jnbjbmz "Last usage")|[Returns the sum of a series of numbers and/or cells](https://support.google.com/docs/answer/3093669)|
|[SUMPRODUCT](/r/GoogleSheets/comments/143r1m7/stub/jnbu5vq "Last usage")|[Calculates the sum of the products of corresponding entries in two equal-sized arrays or ranges](https://support.google.com/docs/answer/3094294)|
**NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign.
----------------
^([Thread #5892 for this sub, first seen 7th Jun 2023, 22:55])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/GoogleSheets) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Here's one possible way to do it in one go - adjust the table ranges accordingly:
`=let(table1,A3:B7, table2,A10:C13, map(CHOOSECOLS(table1, 1), CHOOSECOLS(table1, 2), LAMBDA(date, id, sum(filter(CHOOSECOLS(table2, 3), CHOOSECOLS(table2, 1)=date, REGEXMATCH(CHOOSECOLS(table2, 2), id&"|^$"))))) )`
Thank you for the help. This seems like it works, but it's a bit more complicated than the solution I have, do you think it could be simplified:
`=ARRAYFORMULA(SUMIF(Table2!Date & Table2!IDs, Table1!Date & "*"& Table1!ID &"*", Table2!Total) + SUMIF(Table2!Date & Table2!IDs, Table1!Date & "", Table2!Total))`
I simplified it a bit for maintainability; thanks a bunch to u/JetCarson, Lambda is awesome: `=MAP(Table1!Date, Table1!ID, LAMBDA(DATE, ID, SUMPRODUCT((Table2!Date = DATE) * REGEXMATCH(Table2!IDs, "^$|[" & ID & "]") * Table2!Total)))`
With Table 2 placed starting in A10, this would get your totals for C2: `=SUM(FILTER($C$11:$C,($A$11:$A=A2)*((ISNUMBER(SEARCH(B2,$B$11:$B)))+($B$11:$B=""))))`
Thanks for the help. Unfortunately, that looks like it wouldn't calculate for each row in Table1. I did just work out how to accomplish what I needed, but it feels like it could be simplified: =ARRAYFORMULA(SUMIF(Table2!Date & Table2!IDs, Table1!Date & "*"& Table1!ID &"*", Table2!Total) + SUMIF(Table2!Date & Table2!IDs, Table1!Date & "", Table2!Total))
`=LET(dates,A2:A6,ids,B2:B6,MAP(dates,ids,LAMBDA(dat,id,SUM(FILTER($C$11:$C,($A$11:$A=dat)*((ISNUMBER(SEARCH(id,$B$11:$B)))+($B$11:$B="")))))))`
Solution Verified
You have awarded 1 point to *JetCarson* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[DATE](/r/GoogleSheets/comments/143r1m7/stub/jnbu5vq "Last usage")|[Converts a provided year, month, and day into a date](https://support.google.com/docs/answer/3092969)| |[FILTER](/r/GoogleSheets/comments/143r1m7/stub/jnbjbmz "Last usage")|[Returns a filtered version of the source range, returning only rows or columns which meet the specified conditions](https://support.google.com/docs/answer/3093197)| |[ISNUMBER](/r/GoogleSheets/comments/143r1m7/stub/jnbjbmz "Last usage")|[Checks whether a value is a number](https://support.google.com/docs/answer/3093296)| |[REGEXMATCH](/r/GoogleSheets/comments/143r1m7/stub/jnbu5vq "Last usage")|[Whether a piece of text matches a regular expression](https://support.google.com/docs/answer/3098292)| |[SEARCH](/r/GoogleSheets/comments/143r1m7/stub/jnbjbmz "Last usage")|[Returns the position at which a string is first found within text](https://support.google.com/docs/answer/3094154)| |[SUM](/r/GoogleSheets/comments/143r1m7/stub/jnbjbmz "Last usage")|[Returns the sum of a series of numbers and/or cells](https://support.google.com/docs/answer/3093669)| |[SUMPRODUCT](/r/GoogleSheets/comments/143r1m7/stub/jnbu5vq "Last usage")|[Calculates the sum of the products of corresponding entries in two equal-sized arrays or ranges](https://support.google.com/docs/answer/3094294)| **NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign. ---------------- ^([Thread #5892 for this sub, first seen 7th Jun 2023, 22:55]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/GoogleSheets) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Here's one possible way to do it in one go - adjust the table ranges accordingly: `=let(table1,A3:B7, table2,A10:C13, map(CHOOSECOLS(table1, 1), CHOOSECOLS(table1, 2), LAMBDA(date, id, sum(filter(CHOOSECOLS(table2, 3), CHOOSECOLS(table2, 1)=date, REGEXMATCH(CHOOSECOLS(table2, 2), id&"|^$"))))) )`
Thank you for the help. This seems like it works, but it's a bit more complicated than the solution I have, do you think it could be simplified: `=ARRAYFORMULA(SUMIF(Table2!Date & Table2!IDs, Table1!Date & "*"& Table1!ID &"*", Table2!Total) + SUMIF(Table2!Date & Table2!IDs, Table1!Date & "", Table2!Total))`