Sum only for toggled columns

[DynV]

I plan to make a table which 1st column group is going to be about a base item, further column groups are to be compared to the base one; however I plan to list multiple of them and so I’d like the comparison to only be done to 1. My thought was for a binary column to be included just before the number related to the comparisons. A CSV example: Base label, Base #, Alt(ernative) 1 label, Alt 1 binary, Alt 1 #, Alt 2 label, Alt 2 binary, Alt 2 #, […], # picked, % +/- base.

Do I need to do super long formula in the summary columns (at the end, so to the right), or is there some method to have a smaller formula spread over the columns? An example of the long formula I’m thinking for # picked: if alt 1 # is not empty check if alt 1 binary is true and if so use that, if alt 2 # is not empty check if alt 2 binary is true and if so use that, […], use base # (as no alt was used).

Thank you kindly

[LeroyG]

@DynV,

Can you confirm if the arrangement of the spreedsheet is something like this sample, with the formula in column J?
sum toggled.ods.

EDIT: sum toggled 3 alts.ods with some explanation below the data.

You will need to add a “~” and the new cell reference for each new binary column added. Use of “~” reference Help in LibreOffice.

LibreOffice Help on SUMIFS function.

If you want to add more information, you can edit your question, or comment an answer. Do not use Answer to comment.

Mark the circle to the left of the answer that solves your question.

[DynV]

It it. Thank you for making sure.

[LeroyG]

If you think the answer settles your question, mark the circle to the left of the answer.

[DynV]

I thought you were asking of your example fitted my idea so that if it did, you’d explain.

To see how it would behave with 3 alternates, I deleted the column “[…]”, then copied the 2nd alternate, inserted columns before, pasted them there, and on the old #2 changed the labels to #3. I then looked at the resulting formula which was =IF(SUM(D2~J2)>0,SUMIFS(E2:K2,D2:J2,"1"),B2), whereas before it was =IF(SUM(D2~G2)>0,SUMIFS(E2:H2,D2:G2,"1"),B2). I then realized I needed to test it so changed the #3 label to 3AA and 3rd alt # to 70, then copied the last line, and on that final line deleted the #2 binary, which pick was 70 as expected but I then went to the previous last line deleting the 3rd alt binary and the pick was now the base: 55; I wanted to be the same as prior the adjustment: 60.

If that issue is resolved in your next comment, and if you feel like it, explain how this works or give a URL to such explanation. I was lost looking at ~ for starters, other things puzzle me.

[DynV]

“5 points required to upvote”
Sadly I won’t be able to upvote. Thank you again.

I’m wondering how validation in the formula would be handled, ie: only consider multiples of 5 (so non-multiples of 5 are treated as an empty cell). I don’t–need–to know it, I’m just curious.

[LeroyG]

@DynV,

You can’t upvote yet, but you can still mark the answer as correct. Thanks.