CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
user3268054
user3268054

Reputation: 121

Dynamic Range / Array (Google Sheets)

I'm trying to figure out a way so I can dynamically create a range or array so I can SUM up the values. This is the table where I need to grab the values based on when the player was acquired and/or dropped.

So in the case of Bob, the Total Points received would equal up to 45 as the player was acquired during Week 1, but dropped before the conclusion of Week 4. On the Calculations sheet, cell J4 and K4 would dictate on the cells I need to add. In this case, it would be cells C24:E24 on Traded Player Data sheet.

I don't know if I need to great a different table so I can Query and Sum up the column based on my criteria.

Thank you for any time you use to help me with this problem.

enter image description here

enter image description here

Here is my demo link: https://docs.google.com/spreadsheets/d/10EEhka5NWpaFFPUme_7-mX4MJJTABXlfObvGY7eRYI4/edit?usp=sharing

Upvotes: 0

Views: 573

Answers (2)

Mario Javier Eugenio Estrada
Mario Javier Eugenio Estrada

Reputation: 337

Try this formula (assuming the score data is in the Sheet "Trade Data Input"):

=SUM(INDIRECT("'Trade Data Input'!"&ADDRESS(MATCH(I4,'Trade Data Input'!B:B,0),SUBSTITUTE(J4,"Week","")+2)&":"&ADDRESS(MATCH(I4,'Trade Data Input'!B:B,0),SUBSTITUTE(K4,"Week","")+1)))

You can see in this picture that the formula is working correctly: enter image description here

Upvotes: 2

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(I4:I10, QUERY(SPLIT(FLATTEN(IF('Trade Data Input'!C24:J30="",,
 'Trade Data Input'!B24:B30&"×"&'Trade Data Input'!B24:B30&COLUMN('Trade Data Input'!C23:J23)-
 (COLUMN('Trade Data Input'!C23)-1)&"×"&'Trade Data Input'!C24:J30)), "×"), 
 "select Col1,sum(Col3) 
  where Col2 matches '"&TEXTJOIN("|", 1, IFNA({I4:I10&
 REGEXEXTRACT(J4:J10, "\d+"), IFERROR(I4:I10&
 REGEXEXTRACT(J4:J10, "\d+")+IF(COLUMN(1:1)<
 REGEXEXTRACT(K4:K10, "\d+")-
 REGEXEXTRACT(J4:J10, "\d+"), COLUMN(1:1), 0/0))}))&"'
  group by Col1"), 2, 0)))

enter image description here

Upvotes: 0

Related Questions