If you have a large data set in Excel and you want to calculate totals based on specific criteria, such as sales by region or expenses by department, you can use the SUMIF function. Here’s how:
Open your Excel workbook and select the cell where you want to display the total.
Type =SUMIF( followed by the range of cells that contain the categories you want to sum (e.g., A1:A10), a comma (,), and the criteria you want to use to filter/indicate the category in that range (e.g., "North").
Type another comma (,) followed by the range of cells that contain the values you want to sum (e.g., B1:B10).
Close the parentheses and press Enter.
Your total will be displayed in the selected cell.
Here’s an example formula:
This formula will sum all values in column B (e.g., “Sales”) where the corresponding value in column A is “North.”
Example Data Set
Here’s some sample data I’ll use to demonstrate the SUMIF function’s potential.
Example data set of sales and expenses by region
Here are some examples of how you can use SUM and the SUMIF function with the previous data set example:
Total sales for all regions
Total expenses for all regions
Total sales for the North region
Total expenses for the South region
Total sales greater than $150
Total expenses less than $30
Examples of formulas and results for various needs based on the example data
So now imagine that you have three months’ worth of data and “North” appears three times. Your SUMIF function becomes even more valuable as it only sums the North rows’ values.
Hopefully this helps! Let me know if you have any specific questions about using this function with your data.
If you’re attempting to use Solutions, AI Builder, certain Power Apps templates, etc. you may run into a situation where you’re working in an environment without a Dataverse (formerly known as Common Data Service) database. This will prompt you to create a new environment or database as a power user (if allowed).
Create a new environment with a Power Apps per user plan
Once you’ve been licensed with a Power Apps per user plan, you’ll be able to create a solution and use existing or create new environments unless your admins have limited who can create new environments.
If you’re allowed, you can simply begin building your flow or app as you normally would, but now that you’re licensed appropriately, you’ll be able to choose to create a new environment in-context as you go. For example, let’s say there are no environments with databases you can use for a new Assets Checkout app you want to build from a template in Power Apps. Simply begin building the app from template, and choose to Create new environment when prompted.
Then in the panel that appears to the right, give the environment a name, choose a data region, and environment type. Then choose Create environment.
Verify your currency and language. And if you’re creating a sample app (as we are in this scenario) you’ll also decide whether to bring in the sample data with that template. When satisfied, click Create my database.
Now as you’re building solutions in the Power Platform, you can switch between environments by selecting the name of the active environment and choosing the environment in which you wish to build (or utilize data from).
Now let’s create an environment from the Power Platform admin center as an admin.
Create a new environment as an admin in the Power Platform admin center
This is arguably the better method to use at an organization level to make sure your environments have a consistent naming convention, no efforts are duplicated, and boundaries are clear from one environment to another. With each environment potentially belonging to a different geography depending on how it was set up, this could be a significant compliance concern for some sectors. If you need to restrict environment creation to admins only, check out this other post. Otherwise, let’s proceed with creating a new environment for your users from the Power Platform admin center.
Name and describe your new environment, and choose the region in which its data needs to be stored. You’ll also choose type (Trial, Sandbox, Production, etc.). If this environment is going to be used to store and use data, you’ll want to also enable database creation (creates a Dataverse database). Click Next when ready.
Then choose the language, currency, and additional options (including restricting usage to a specific security group). Click Save when ready.
You (and/or your organization’s users whom are allowed to use the environment) will now be able to connect to the environment and any of the tables within its Dataverse database. This Dataverse connection can be used in Dynamics 365 apps, Power Apps portals and apps, and Power Automate flows.
Data gateways allow users to connect online services, such as Power BI service, Power Automate, and Power Apps to on-prem data sources such as SQL databases, SharePoint server lists and libraries, and network shares.
As you can imagine, you wouldn’t want everyone installing their own individual gateways throughout your organization. Managing and sharing those centrally is much more efficient (and secure). You can manage who is allowed via the Power Platform admin center at admin.powerplatform.microsoft.com.
Note: You must be one of these roles to restrict gateway installers:
Azure AD Global administrator
Office 365 Global admin
Power BI service administrator
Restricting installations does not impact gateway administration. You can assign and re-assign users to administer and use gateways at any time. The following steps are strictly to manage who is able to install an enterprise gateway on a machine.
A simple trick in Excel allows you to remove duplicate values from a column in Excel. Depending on your version of Excel, yours may look a bit differently but the process is nearly the same. Below, I’m using Excel 2016.
1. Select the column, or the values from which you’re removing duplicates. Note: You could also just start with step 2, and then select the relevant column(s) from the resulting dialog. 2. Go to Data > Remove duplicates (in the Data Tools panel)
3. Confirm if your column has a header (column title or not) and click OK.
4. Excel will confirm the number of duplicates found and how many remain. Click OK.
Automatically create and send reports, files and lists on a regular schedule using Microsoft Flow’s recurrence trigger. Whether hourly, daily, weekly or monthly you can deliver the most current and relevant data from SharePoint or OneDrive to interested parties via email without lifting a finger. Combine this with calculated columns in SharePoint and conditions for some awesome possibilities:
Report costs or expenditures above a certain amount
Current month’s birthdays and/or workiversaries to your secretary
Send expenses per department or individual to that department or individual
Budget and salary or payroll figures weekly
Notify when an open ticket is idle for a week or incomplete
Upcoming events per location
Share evaluation status with supervisors for just their employees
Recently closed deals and contracts
Survey responses or reviews under 3 stars
Upcoming deadlines per department
Client info and updates to proper salespeople based on location or product
Distribute new hires’ contact/location info to the organization in weekly batches
Microsoft Flow mobile buttons are magical. One touch on your mobile device, and gears start turning to retrieve and deliver the data you need when and how you need it. Recently, I set out to deliver all Microsoft Forms responses to a recipient on-demand as an excel file using a Microsoft Flow mobile button they could press whenever they wanted the results. I also created a button someone could use to be sent all the birthdays coming up in the next week for our organization whenever they need it. You can adjust the following steps to fit your situation and tools, but the following outlines two ideas:
Sending someone all responses to a Microsoft Forms survey whenever they press the button (Take a snapshot in time of responses, or pull up-to-the-minute feedback into your meeting)
Sending someone SharePoint list items in an excel sheet that match a certain criteria (Projects ending in the next two weeks)
Even if your SharePoint site’s regional settings are correct (or whichever data source you’re pulling from), Power BI could convert it to the wrong time zone upon import. It’s a quick fix, luckily. Instead of using your “modified,” “created” or other date field in your report, we’ll create a new calculated column in Power BI to use with an accurate time zone.
Note: This, like many O365 things, is rapidly evolving. If you’re aware of better practices or new updates to licensing, feel free to mention it in comments.
I’m currently at SharePoint Fest Seattle where Chris McNulty, Sr. Product Manager for Office 365 and SharePoint at Microsoft, mentioned (as I understand) there could be changes coming to licensing that would allow more people to consume Power BI reports in a friendlier (more affordable) licensing structure. This would be amazing because currently:
I can create reports. People can’t view data in those reports in a secure way because the entire organization isn’t licensed for Power BI per person above the “free” license.
Specifically I, with a Power BI Pro license, can create reports and place those in SharePoint’s new page experience Power BI web parts (in Preview) but other people (with free or without Pro licenses) cannot view them. They see the following:
Of course, to me as the creator and properly-licensed individual, I see the report perfectly embedded as it should be. And not every organization can afford to license every single user appropriately to be able to simply view embedded reports. Especially if consuming reports (not sharing or building) is the only function they need in the Power BI realm.
In this post, I’ll cover:
How to embed Power BI reports the normal, easy (but license-exclusive) way
Why the webpart (normal, easy way) is cooler than embedding a script
How to embed the report in a (less secure) way so that non-licensed or free-license individuals can actually view and manipulate the data
One of my favorite features of Power BI is the ability to have published reports automatically refresh data on a schedule. This is great for “setting and forgetting” your reports, knowing wherever you publish them they will be showing the most recent data for your clients. I feel like it used to be depending on your license, you could be limited to how frequently you can refresh (max of once per day), but you can refresh nonetheless. And this may have changed, as I couldn’t find (in my brief search) any confirming statement.