Microsoft Office (Session 06)

Shape Image One

ADVANCED
LOGIC

SUMIF and SUMIFS

These formulas help you find the sum of values based on specific criteria. Here’s a quick breakdown:

SUMIF:

Ideal for summing values based on one condition.

Structure:

=SUMIF(range, criteria, [sum_range])

  • range: The cells containing the values you want to sum.
  • criteria: The condition your values must meet (text, number, or formula). Can include wildcards (* or ?) for partial matches.
  • [sum_range] (optional): The specific cells to sum if different from the range. If omitted, the range is used.

Example:

Imagine you have a list of sales figures (column A) and product categories (column B). You want the total sales for “Electronics” products.

Formula:

=SUMIF(B2:B10, “Electronics”, A2:A10)  **Assuming categories in B2:B10 and sales in A2:A10**

SUMIFS:

Powerful for summing values based on multiple conditions.

    Structure:
    • =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

      • sum_range: The cells containing the values you want to sum.
      • criteria_range1, criteria2, …: The cell ranges containing the data you’re applying conditions to.
      • criteria1, criteria2, …: The conditions your values must meet in the corresponding criteria ranges.

      Example:

      Building on the previous example, you want the total sales for “Electronics” products exceeding $1000.

      Formula:

      =SUMIFS(A2:A10, B2:B10, “Electronics”, A2:A10, “>1000”)

      Key Differences:

      Feature

      SUMIF

      SUMIFS

      Number of Conditions

      One

      Multiple

      Criteria Range Size

      Must match sum_range

      Can differ from sum_range

      Tips:
      • Use the Formula Help (Fx button) for detailed information on both functions.
      • Search online for “[Excel SUMIF formula] + example” and “[Excel SUMIFS formula] + example” to find tutorials with more complex scenarios.

      By mastering SUMIF and SUMIFS, you can efficiently calculate conditional sums in your spreadsheets, leading to valuable insights from your data.

      Videos:
      Optimized by Optimole
      WhatsApp whatsapp
      Messenger messenger
      Instagram instagram
      Call Us phone
      chat