10 Replies to “How to Sum Values by Category in Excel”

1. Perfect. Exactly what I’m look for. Thank you!

   Reply

2. Hey there,

   Awesome article, it just unstuck me!

   Super grateful.

   But why is there $ for the ranges?

   Many thanks,
   Remi

   Reply

3. This worked great for me – I was trying to get a better idea of my budget items and this gave me exactly what I wanted – THANK YOU!

   Reply

   1. You are very welcome Frank! Thank you for the feedback!

      Reply

4. Hello, I think I did the formula right on mine, but everything is just 0.

   Reply

   1. Hi Maria…When summing values by category in Excel, there are a few common approaches, such as using `SUMIF` or `SUMIFS`. If the result is zero, here are some points to check and a formula you can try:

      ### 1. **Ensure Data Types are Correct**
      – **Check that the values you’re trying to sum are actually numbers**. Sometimes numbers formatted as text cause the sum to return zero.
      – To check this, select the cells and ensure they’re formatted as “Number” in the Excel toolbar.

      ### 2. **Use the Correct Formula**
      – If you want to sum values in `Column B` based on categories in `Column A`, use:
      “`excel
      =SUMIF(A:A, “CategoryName”, B:B)
      “`
      – Replace `”CategoryName”` with the specific category you want to sum, and adjust the column references if your data isn’t in `A` and `B`.

      ### 3. **Using `SUMIFS` for Multiple Criteria**
      – If you need to sum values based on multiple conditions, use:
      “`excel
      =SUMIFS(B:B, A:A, “CategoryName”, C:C, “AnotherCondition”)
      “`
      – Replace `”CategoryName”` and `”AnotherCondition”` with your actual criteria.

      ### 4. **Check for Hidden Rows or Filtered Data**
      – If some rows are hidden or filtered, `SUMIF` and `SUMIFS` will still count those values unless you use `SUBTOTAL`. Hidden rows can lead to unexpected zero sums if there’s a misunderstanding of what’s visible.

      ### 5. **Troubleshoot with Sample Data**
      – Try testing the formula with simplified sample data in a blank sheet to confirm it works as expected.

      Let me know if adjusting any of these steps helps or if you’d like further troubleshooting!

      Reply

5. I think there is a error:
   =SUMIF($B$2:$C$12, E2, $C$2:$C$12)
   should be
   =SUMIF($B$2:$B$12, E2, $C$2:$C$12)
   ?

   Reply

   1. Hi Laurie…Yes, you are absolutely correct! The correct formula should be:

      “`excel
      =SUMIF($B$2:$B$12, E2, $C$2:$C$12)
      “`

      ### Explanation:
      – `$B$2:$B$12`: This is the **range** where Excel looks for the category values (e.g., “Fruits”, “Vegetables”, etc.).
      – `E2`: This is the **criteria**, meaning Excel sums only the values that match this category.
      – `$C$2:$C$12`: This is the **sum range**, where Excel adds up the corresponding values.

      In the incorrect formula:

      “`excel
      =SUMIF($B$2:$C$12, E2, $C$2:$C$12)
      “`
      The issue is that the first argument (`$B$2:$C$12`) covers two columns, which is incorrect because SUMIF expects a **single-column range** for criteria matching.

      Your correction makes the formula work as intended by ensuring that Excel correctly matches the category in column **B** while summing values in column **C**. ✅

      Reply

6. please I need a formula or sample for monthly calculation of visits to schools weekly. So we have 8 groups that visits schools weekly in their unique communities. records are taken of number students in each school each week. what formula can help calculate total number of students and schools without repeating same same numbers in sane schools the next weeks. to know the actual number of students visited once In each school in a month

   Reply

   1. Hi Frank…Hopefully the following process will be helpful.

      —

      ## How to Calculate Monthly School Visits Without Double Counting Students

      When school visits happen every week, one common challenge is avoiding double counting.

      For example:

      * You may visit the same school every week
      * You record the number of students each week
      * But when reporting monthly results, you only want to count:

      * each school once
      * each student once

      In other words, even if a school is visited four times in a month, its students should only be counted one time.

      Below is a simple and reliable way to do this in Excel.

      —

      ## Recommended Data Structure

      Set up your data like this (preferably as an Excel Table):

      | Date | Group | Community | School | Students |
      | ———- | ——- | ——— | ——– | ——– |
      | 2026-01-03 | Group 1 | East | School A | 320 |
      | 2026-01-10 | Group 1 | East | School A | 320 |
      | 2026-01-17 | Group 1 | East | School A | 320 |
      | 2026-01-05 | Group 2 | West | School B | 280 |

      Even though School A appears multiple times, the student number represents the same students, not new ones.

      —

      ## Goal

      At the end of the month, we want to know:

      * How many unique schools were visited?
      * How many unique students were reached — counted once per school?

      —

      ## Step 1: Select the Month

      In any cell (for example H2), enter a date within the month you want to analyze:

      2026-01-01

      Excel will use this to identify all records that belong to January 2026.

      —

      ## Step 2: Count Unique Schools Visited That Month

      Use this formula:

      =COUNTA(
      UNIQUE(
      FILTER(Table1[School],
      EOMONTH(Table1[Date],0)=EOMONTH(H2,0))
      )
      )

      What this formula does:

      * Filters records for the selected month
      * Removes duplicate school names
      * Counts each school only once

      Result example:

      42 schools visited in January

      —

      ## Step 3: Count Unique Students Reached (No Repeats)

      This formula counts students only once per school, even if the school was visited weekly.

      =LET(
      m, EOMONTH(H2,0),
      d, FILTER(Table1, EOMONTH(Table1[Date],0)=m),
      schools, CHOOSECOLS(d, XMATCH(“School”, Table1[#Headers])),
      students, CHOOSECOLS(d, XMATCH(“Students”, Table1[#Headers])),
      u, UNIQUE(schools),
      SUM(XLOOKUP(u, schools, students))
      )

      What this formula does:

      1. Filters data to the selected month
      2. Identifies unique schools
      3. Retrieves student count for each school once
      4. Adds them together

      —

      ## Example Result

      Even if the data shows:

      * School A visited 4 times (320 students each week)
      * School B visited 3 times (280 students each week)

      The monthly report will show:

      * Schools visited: 2
      * Students reached: 600

      Not 2,320 — because students were not counted repeatedly.

      —

      ## Why This Method Works

      This approach ensures:

      * Weekly activity tracking stays detailed
      * Monthly reporting reflects actual reach
      * No inflation of numbers
      * Accurate donor, government, or program reports

      —

      ## Summary

      If you conduct weekly school visits and want accurate monthly reporting:

      * Record visits weekly as usual
      * Count schools using UNIQUE
      * Count students by summing one value per school
      * Avoid adding weekly totals directly

      This method gives you the true number of students reached per month, not the number of visits.

      —

      If you want, I can also help you create:

      * a simplified version using Pivot Tables
      * a version compatible with older Excel
      * an automated monthly summary dashboard
      * formulas separated by community or by group

      Just tell me.

      Reply