0

I'm needing a formula or code that will allow the following to happen if possible.

  • First search column C for a value (Wk1,Wk2,Wk3,Wk4 or Wk5)
  • then show the sum of column O In cell X15
  • the formula should only sum the column up to the point Wk 2 shows in Column C

Something to note is that Column C will have a dropdown menu in each cell so the user can select when Wk1 or Wk2 etc starts so these values can move around rather than being a fixed distance apart.

Hope this makes sense and the sample image shows but if not please let me know and I'll do my best to explain further.

Thanks in advance.

Link to Spreadsheet sample image here

2
  • Welcome to Super User. Please read the tags before posting your question. You are asking an off-topic question. See help center. Jan 13, 2023 at 11:25
  • Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Jan 13, 2023 at 12:00

1 Answer 1

0

Clear cells W13:Y100 and put this formula in cell W13:

=scan( 
  "Fill down", C13:C, 
  lambda( 
    previous, current, 
    if( 
      row(current) > max( index(row(O13:O) * (O13:O <> "")) ), 
      iferror(1/0), 
      if( len(current), current, previous ) 
    ) 
  ) 
)

...and this formula in cell X13:

=query( 
  O13:W, 
  "select lower(W), sum(O) 
   where W is not null 
   group by lower(W) 
   label lower(W) '', sum(O) 'Sales' ", 
  0 
)

...to get results like these:

Sales
wk1 3
wk2 7
wk3 5
3
  • Perfect, thank you so much - You have saved the day. :-) Jan 13, 2023 at 12:51
  • Think i'm being a bit thick here but what would i need to do to the formula so as to move the results. The reason being as I was trying to duplicate it next to the results of this but amend it so that it picks up repairs only and then again for revenue but when i move it the formula stops working and nothing i do seems to rectify it? Jan 13, 2023 at 14:18
  • To refer to some other columns, you need to modify column references in the query() statement. Please ask only one question per post. Post a new question if necessary. Jan 13, 2023 at 15:06

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.