How to use Excel’s SUMIF function to calculate totals by category

four trays of varieties of fruits

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:

  1. Open your Excel workbook and select the cell where you want to display the total.
  2. 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").
  3. Type another comma (,) followed by the range of cells that contain the values you want to sum (e.g., B1:B10).
  4. Close the parentheses and press Enter.

Your total will be displayed in the selected cell.

Here’s an example formula:

=SUMIF(A2:A5,"North",B2:B5)

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.

ABC
1RegionSalesExpenses
2North10020
3South20030
4East15025
5West17535
Example data set of sales and expenses by region

Example Scenarios

Here are some examples of how you can use SUM and the SUMIF function with the previous data set example:

ScenarioFormulaResult
Total sales for all regions=SUM(B2:B5)625
Total expenses for all regions=SUM(C2:C5)110
Total sales for the North region=SUMIF(A2:A5,"North",B2:B5)100
Total expenses for the South region=SUMIF(A2:A5,"South",C2:C5)30
Total sales greater than $150=SUMIF(B2:B5,">150")375
Total expenses less than $30=SUMIF(C2:C5,"<30")45
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.

References

Set multiple choices as default values for checkbox fields in SharePoint forms

default checks

I recently worked on a project for a client that needed 2/3 checkbox options checked by default on a new form. While not as straight-forward as other field types, it’s still certainly possible.

  1. Go to List –> List Settings (server/on-prem) or settings wheel –> List Settings (online/O365)
    list settings
    list settings o365
  2. Click the name of your checkbox/choice column to edit its settings or create a new checkbox column
    iexplore_2018-07-26_07-53-51
  3. Change “Default value:” from “Choice” to “Calculated Value”
    iexplore_2018-07-26_07-52-49
  4. Enter a formula like: =”;#Choice;#Choice;#Choice;#”
    Example:
    =”;#Printed Statement;#E-Statement;#Pick Up in Office;#”
  5. Click OK

That’s it! Now on new forms/items, everything you entered in the formula as a default choice will be pre-checked.
pre-checked

Power BI: Calculate next year’s amount in previous year’s row

next year

The following is a DAX formula you can use to create a calculated column that shows “next year’s” value in “this year’s” row. You can easily adapt this to show “yesterday’s” amount or “tomorrow’s” total as well. It can be modified for days, weeks, months, etc. as long as the time measure is able to be sorted sequentially.

Continue reading “Power BI: Calculate next year’s amount in previous year’s row”

Using today’s date and/or current time in calculated columns and list view filters

I previously shared how to create a “Today” column in SharePoint that would always be up-to-date even if list items weren’t modified. These were no-code solutions that utilized either SharePoint Designer or Microsoft Flow. You can, however, use Today’s date/time to create views and calculated columns without workflow or script or the need to create another column.

Continue reading “Using today’s date and/or current time in calculated columns and list view filters”