Google Sheets - Formula or Code?

Question

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

Answer 1

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