Include the value of a Cell in a SUM based on the selection of a radio button

[Excel_User_10k]

I am looking for a Formula that will count a price based on the selection of a few Radio Buttons.

So for instance =SUMIF(B1+(Radio selection 1=1, then add B2, if radio selection 2=1, add B3...etc I know this isn't the correct use of SUMIF but this is the essence of what I am trying to achieve.

Is this possible? I have found it asked in places but never actually answered.

 

[jasonb75]

The easiest way that comes to mind is to use SUM(INDEX( in conjunction with a link cell for each group of option buttons.

=SUM(INDEX(B2:B4,D1)) where D1 is the link cell to a group of 3 option buttons with B2:B4 being the range of cells to look at.

If you need to use non contiguous cells, or if the order of assignment needs to be changed then use CHOOSE instead of INDEX. =SUM(CHOOSE(D1,B2,B3,B4))

 

[Excel_User_10k]

Hi Jason,

Thanks for the suggestion. Unfortunately it isn't either or. It is more like AND and AND. So it doesn't work if the reference for all radio's are in the same cell. Is it possible to do it where the Radio button references are separate too? I can't have them in the same cell ref for other reasons too. So say:

Always add C1 then...

If A2=2 Then add value of B2
If A3=2 Then add value of B3
If A4=2 Then add value of B4

So it could result in only (C1+B3), (C1+B2+B3), (C1+B3+B4), etc.

By the sounds of it this isn't possible though.

 

[jasonb75]

Are you using option buttons or checkboxes?

Checkboxes return TRUE or FALSE to their respective link cells, radio buttons return a number depending on which button is selected within their respective group.

If you have multiple groups then you can simply add multiple INDEX or CHOOSE functions. The suggestion that I provided was a simple example based on the information in your question but your example is too simple to figure out exactly what is required.

If A2=2 Then add value of B2
If A3=2 Then add value of B3
If A4=2 Then add value of B4

What if A2, A3, and A4 contained 1 or 3 instead?

 

[Excel_User_10k]

Are you using option buttons or checkboxes?

Checkboxes return TRUE or FALSE to their respective link cells, radio buttons return a number depending on which button is selected within their respective group.

If you have multiple groups then you can simply add multiple INDEX or CHOOSE functions. The suggestion that I provided was a simple example based on the information in your question but your example is too simple to figure out exactly what is required.

What if A2, A3, and A4 contained 1 or 3 instead?

Because again, if it was 1, 2 or, 3 in the same reference it could only ever be OR, as it can only be one or the other. By some miracle I seem to have figured it out. It's a bit long winded but it works. It is a combination of SUM and IF. Hopefully you think it is stable enough?

=SUM(C1+(IF(A2=2,B2,"0")+IF(A3=2,B3,"0")+IF(A4=2,B4,"0")+IF(A5=2,B5,"0")))

 

[jasonb75]

If that's all you need then the simple version would be

=C1+SUMIF(A2:A5,2,B2:B5)

Looking at what you have done, I believe that I was overthinking the problem based on the ways that I see option buttons used more often.

 

[Excel_User_10k]

If that's all you need then the simple version would be

=C1+SUMIF(A2:A5,2,B2:B5)

Looking at what you have done, I believe that I was overthinking the problem based on the ways that I see option buttons used more often.

Ah ok no worries. Yeah that is much simpler haha! Thanks very much

Out of curiosity, how would this work if the radio references or SUM references weren't sequential?