0

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

Improve this question
3
  • 1
    Would it be possible for you to share a sample sheet? Share a test sheet Commented Aug 24, 2021 at 18:59
  • Sample Sheet Link : Here is my demo link: docs.google.com/spreadsheets/d/… Commented Aug 25, 2021 at 0:36
  • I did get it working, sort if. With the help of some helper cells (See test sheet). I'm assuming there's a more elegant way of doing it though. Commented Aug 25, 2021 at 12:33

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You are correct, the score data is in the Trade Data Input sheet. When I use this formula, I get a result of 0.
I only have Bob once in the column. I posted a test sheet above.
I have update my initial answer with a working formula which have been tested on the sheet that you shared on your query.
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.