- Power Apps Community
- Get Help with Power Apps
- Building Power Apps
- Microsoft Dataverse
- AI Builder
- Power Apps Governance and Administering
- Power Apps Pro Dev & ISV
- Power Apps Portals
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Community Blog & News
- News & Announcements
- Power Apps Community Demo Extravaganza 2020
- Galleries
- Community App Samples
- Webinars and Video Gallery
- Canvas Apps Components Samples
- Kid Zone
- Business Value Webinars and Video Gallery
- Emergency Response Gallery
- 2019 Microsoft Business Applications Summit Recordings (Archived)
- Microsoft Business Applications Summit 2020 Session Recordings
- Ideas
- Power Apps Ideas
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Power Apps Community
- Forums
- Get Help with Power Apps
- Building Power Apps
- Sum( If( IsBlank assistance

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Sum( If( IsBlank assistance

04-09-2018
01:33 PM

Hello,

I have a series of labels that are fed from a LookUp function. I want to have a count at the end of the form which shows users the #/Total labels that are filled.

If label has no text, it receives a "0". If it has text, it receives a "1". I want to sum the "1" 's at the end of the row like so:

**NOTE**: The count below should read "2/13" because 2 labels have text.

Here is the function I have so far, but its not giving me a sum of all the "1"'s.

Sum( If( IsBlank( label_complianceComp1 || label_complianceComp2 || label_complianceComp3 || label_complianceComp4 || label_complianceComp5 || label_complianceComp6 || label_complianceComp7 || label_complianceComp8 || label_complianceComp9 || label_complianceComp10 || label_complianceComp11 || label_complianceComp12 || label_complianceComp13 ), 0,1) ) & "/13"

Can someone give me a pointer on what this function is missing?

Thank you

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-09-2018
04:06 PM

Hello ericonline,

Interesting problem!

The **Sum()** function takes 1 or more arguments and sums them up. What's happening here is the** ||** (Or) function is returning false, because none of the controls are **true** (they can't be, they're controls. This is different from programming in JavaScript, for example, which evaluates whether operands are truthy or falsey. PowerApps just evaluates whether it's actually **true** here). So, we get the following:

**Sum( If( IsBlank(false), 0, 1) )**

**Sum( If( false, 0, 1) )**

**Sum( 1 )**

**1**

So that's why you're experiencing what you are. But how do we fix it? I feel like there should be a nice elegant solution, but I haven't been able to come up with it, yet. I do know a solution, which I'll share, but if someone figures out a better one, please feel free to add it to the discussion!

Anway, I think the following will work for you:

Sum( ForAll( [ Value(label_complianceComp1.Text), Value(label_complianceComp2.Text), Value(label_complianceComp3.Text), Value(label_complianceComp4.Text), Value(label_complianceComp5.Text), Value(label_complianceComp6.Text), Value(label_complianceComp7.Text), Value(label_complianceComp8.Text), Value(label_complianceComp9.Text), Value(label_complianceComp10.Text), Value(label_complianceComp11.Text), Value(label_complianceComp12.Text), Value(label_complianceComp13.Text) ], If( !IsBlank(Value), 1, 0 ) ), Value ) & "/13"

What this does is it gets the **Text** property of each label and finds the value of that using the **Value()** function. It makes a table of these values (basically a list). Then, it uses the **ForAll()** function to loop over these values and return a table of 1's and 0's (1 if not blank, 0 if blank). Then, it uses the version of the **Sum()** function that works on tables ( **Sum(table, expression)** ) to sum all of these values up. Then it joins it to "/13" like you were doing before.

So, that will work. One thing you might want to consider, though, is making a horizontal gallery instead of all of these labels. If you can give the horizontal gallery a table of the values as text, you can just have one label inside the gallery with **Text=ThisItem.Value** and the sum expression can simplify to (assuming it's still strings of non-zero numbers or blank):

Sum( ForAll( MyGallery.AllItems, If( !IsBlank(Value), 1, 0 ) ), Value ) & "/" & CountRows(MyGallery.AllItems)

Finally, here's some relevant documentation links, if you want to learn more:

Sum Function (and other similar aggregate functions)

CountRows Function (and other similar functions)

Anyway, I hope this helps!

Software Engineer on PowerApps

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-09-2018
04:06 PM

Hello ericonline,

Interesting problem!

The **Sum()** function takes 1 or more arguments and sums them up. What's happening here is the** ||** (Or) function is returning false, because none of the controls are **true** (they can't be, they're controls. This is different from programming in JavaScript, for example, which evaluates whether operands are truthy or falsey. PowerApps just evaluates whether it's actually **true** here). So, we get the following:

**Sum( If( IsBlank(false), 0, 1) )**

**Sum( If( false, 0, 1) )**

**Sum( 1 )**

**1**

So that's why you're experiencing what you are. But how do we fix it? I feel like there should be a nice elegant solution, but I haven't been able to come up with it, yet. I do know a solution, which I'll share, but if someone figures out a better one, please feel free to add it to the discussion!

Anway, I think the following will work for you:

Sum( ForAll( [ Value(label_complianceComp1.Text), Value(label_complianceComp2.Text), Value(label_complianceComp3.Text), Value(label_complianceComp4.Text), Value(label_complianceComp5.Text), Value(label_complianceComp6.Text), Value(label_complianceComp7.Text), Value(label_complianceComp8.Text), Value(label_complianceComp9.Text), Value(label_complianceComp10.Text), Value(label_complianceComp11.Text), Value(label_complianceComp12.Text), Value(label_complianceComp13.Text) ], If( !IsBlank(Value), 1, 0 ) ), Value ) & "/13"

What this does is it gets the **Text** property of each label and finds the value of that using the **Value()** function. It makes a table of these values (basically a list). Then, it uses the **ForAll()** function to loop over these values and return a table of 1's and 0's (1 if not blank, 0 if blank). Then, it uses the version of the **Sum()** function that works on tables ( **Sum(table, expression)** ) to sum all of these values up. Then it joins it to "/13" like you were doing before.

So, that will work. One thing you might want to consider, though, is making a horizontal gallery instead of all of these labels. If you can give the horizontal gallery a table of the values as text, you can just have one label inside the gallery with **Text=ThisItem.Value** and the sum expression can simplify to (assuming it's still strings of non-zero numbers or blank):

Sum( ForAll( MyGallery.AllItems, If( !IsBlank(Value), 1, 0 ) ), Value ) & "/" & CountRows(MyGallery.AllItems)

Finally, here's some relevant documentation links, if you want to learn more:

Sum Function (and other similar aggregate functions)

CountRows Function (and other similar functions)

Anyway, I hope this helps!

Software Engineer on PowerApps

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-09-2018
06:21 PM

Hello Paul,

Thank you for the detailed reply. I really appreciate it and yes, your solution to sum all labels worked great.

Since you are familiar with this puzzle piece, I'd like to go out on a limb and push the conversation a bit further as I'm really stuck on the label query itself:

The labels are already part of a *vertical *Gallery as shown here. I'm nervous about nesting Galleries as I don't fully understand them.

Each label is looking up records in a Sharepoint list like so. The "." notation on the end is very strange to me!

Here is the Sharepoint list:

**The label query is not correct nor complete.**

- Not correct because as you can see in the Sharepoint List, rdm_cost_center S0801 completed comp1_num on 6/1/2018 but its not showing in the label.
- I thought LookUp would go down the column and pull the first record it found. This is not the case here.

- Not complete because the query is not comparing the current year, half, or month against the records to populate the label.
- I have global variables set for current year, current half, and current month in the app, but how do I compare them against the same values in each record, then return the results?

There is a writeup on the issue here as well with a bit different context. Perhaps thats a better place to take this (if you'll come along 🙂 ).

Its very cool how PowerApps opens up development to a population of folks who would otherwise not be engaged. Thank you for the very useful product you've developed. I'll be honest and say its right up there with OneNote in my top 2 products that Microsoft makes.

Announcements

Top Solution Authors

User | Count |
---|---|

194 | |

126 | |

89 | |

48 | |

42 |

Top Kudoed Authors

User | Count |
---|---|

280 | |

160 | |

137 | |

82 | |

77 |