cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gazzo1967
Impactful Individual
Impactful Individual

Sum Selected values from choice fields

Hi

How do i sum the selected text values from choice fields?

 

My scenario is

combobox1 . = completed, not completed, in progess

combobox2  = completed, not completed, in progess 

combobox3  = completed, not completed, in progess

 

i want to Sum the number of times each value has been selected by count into 3 textboxes

 

Sum(If(Value(combobox1.Selected.Value) = "completed"),1,0)

 

I know i have to use 

SUM() with combobox.selected.value = "completed" and combobox2.selected.value = "not completed"

but i cant get the syntax for the formula right

 

Any help appreciated

Gary

1 ACCEPTED SOLUTION

Accepted Solutions
rebeccas
Community Champion
Community Champion

To do the Sum you use "," instead of the && so like this:

 

Sum(If(cb_1.Selected.Value="Completed",Value("1"),Value("0")),If(cb_2.Selected.Value="Completed",Value("1"),Value("0")),If(cb_3.Selected.Value="Completed",Value("1"),Value("0")), If(cb_4.Selected.Value="Completed",Value("1"),Value("0")), If(cb_5.Selected.Value="Completed",Value("1"),Value("0")))

View solution in original post

9 REPLIES 9
rebeccas
Community Champion
Community Champion

Try using CountIf instead.

gazzo1967
Impactful Individual
Impactful Individual

@rebeccas 

hi

i thought countif counts a column.

i want to sum the number of times each combobox value is selected.

 

 

rebeccas
Community Champion
Community Champion

Yes and no...if you put them in a gallery you could also use it.

 

If they are just free standing you could either try setting a variable that does the count...something like:

 

OnVisible=Set(varCount,Value("0"))

 

ComboBox1-OnChange = If(ComboBox1.Selected.Value="Completed", Set(varCount, Value(varCount + 1)),ComboBox1.Selected.Value="Not Completed", Set(varCount, Value(varCount - 1)))

 

But for that you would have to think through it not subtracting if they select 'Not Completed' right off...I know you can do that but I'm not thinking of how right off.

 

You could also do hidden labels that are next by the combo box that has an if statement to store a value and then store those. If(ComboBox1.Selected.Value="Completed", Value("1"), Value("0")) ....and then add those.

 

Or the rating hidden that has that same if statement above because those are naturally stored as values and easy to sum from.

 

The only way I think you can use the Sum is if you have the combo boxes putting values into a collection...which is another option. 

 

Sorry...lot's of choices. Just depends on how many you have to me. If you have less than 10 I would do the label that is hidden and stores value and add those and more than that I would put those selections into a collection where you could have the values in a separate column. 

 

Hope I wasn't just more confusing!!

 

 

gazzo1967
Impactful Individual
Impactful Individual

@rebeccas 

Thank you so much for the response 🙂

 

i have 5 comboboxes

how would i chain them together in a formula?

i tried

Sum(If(DataCardValue5.Selected.Value="completed", Value("1"),Value("0")
&& DataCardValue6.Selected.Value = "completed",Value("1"), Value("0")
&& DataCardValue7.Selected.Value = "completed",Value("1"), Value("0")))

 

this just gives me a count of '1' even if all 3 boxes are selected completed

 

Sorry im struggling with this one and no doubt its a simple solution 😞

Gary

 

 

 

 

 

gazzo1967
Impactful Individual
Impactful Individual

@rebeccas 

my actual combobax values are these:

DataCardValue5.Selected.Value="a) read the instructions first", Value("1"), Value("0")

DataCardValue6.Selected.Value = "a) look at a map",Value("1"), Value("0")

DataCardValue7.Selected.Value = "a) follow a written recipe",Value("1"), Value("0")

rebeccas
Community Champion
Community Champion

To do the Sum you use "," instead of the && so like this:

 

Sum(If(cb_1.Selected.Value="Completed",Value("1"),Value("0")),If(cb_2.Selected.Value="Completed",Value("1"),Value("0")),If(cb_3.Selected.Value="Completed",Value("1"),Value("0")), If(cb_4.Selected.Value="Completed",Value("1"),Value("0")), If(cb_5.Selected.Value="Completed",Value("1"),Value("0")))

View solution in original post

rebeccas
Community Champion
Community Champion

That format makes it look confusing...this:

 

Sum(

 If(cb_1.Selected.Value="Completed", Value("1"), Value("0")),

 If(cb_2.Selected.Value="Completed", Value("1"), Value("0")),

 If(cb_3.Selected.Value="Completed", Value("1"), Value("0")),

 If(cb_4.Selected.Value="Completed", Value("1"), Value("0")),

 If(cb_5.Selected.Value="Completed" , Value("1"), Value("0"))

)

gazzo1967
Impactful Individual
Impactful Individual

@rebeccas 

Thank you :0)

I can't believe it was using multiple IF... i knew i was missing something silly 😞

 

I'm very grateful for you help

 

Gary 🙂

rebeccas
Community Champion
Community Champion

Your welcome...sometimes you don't have to do the multiple Ifs but I think on this you will. 

 

A lot of times even if I don't have to do it multiple times I will first to make sure I am thinking it through and then remove them. With it just being the 5 I would just leave it though. 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,091)