# Google Sheets - Formula or Code?

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.

• Welcome to Super User. Please read the tags before posting your question. You are asking an off-topic question. See help center. Commented 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. Commented Jan 13, 2023 at 12:00

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
• Perfect, thank you so much - You have saved the day. :-) Commented 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? Commented 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. Commented Jan 13, 2023 at 15:06