SUMPRODUCT Function in Excel: A Step-by-Step Guide for Beginners

The SUMPRODUCT function in Excel multiplies corresponding values in given arrays or ranges and then sums the results. It is often used for complex calculations, data analysis, and performing conditional summing without needing to use an array formula. This versatile function can handle multiple criteria and is particularly useful for tasks like weighted averages or summing products of data points across different ranges.

Sumproduct Formula:

=SUMPRODUCT(SalesTbl[Sales],--(TEXT(SalesTbl[Date],"mmm")="Jan"))

This Excel formula uses the SUMPRODUCT function to calculate the sum of sales from a table, SalesTbl, where the month of the date matches “Jan”. Let’s break it down step by step:

Watch more tutorials on Our Youtube Channel

Sumproduct Function Explanation:

1. SalesTbl[Sales]:
   • This refers to the column in the SalesTbl table that contains the sales figures. It acts as an array of numbers.
2. SalesTbl[Date]:
   • This refers to the column in the SalesTbl table that contains the dates associated with each sale.
3. TEXT(SalesTbl[Date],"mmm"):
   • The TEXT function converts the date values into a text string representing the month in a three-letter format (e.g., “Jan” for January, “Feb” for February).
4. TEXT(SalesTbl[Date],"mmm")="Jan":
   • This compares each converted month to the string “Jan”. The result is an array of TRUE or FALSE values, where TRUE corresponds to dates in January and FALSE to other months.
5. --(TEXT(SalesTbl[Date],"mmm")="Jan"):
   • The double negative -- converts the TRUE and FALSE values into 1 and 0, respectively. This is necessary because SUMPRODUCT works with numeric arrays, not logical values.
6. SUMPRODUCT(SalesTbl[Sales],--(TEXT(SalesTbl[Date],"mmm")="Jan")):
   • SUMPRODUCT multiplies corresponding elements of the two arrays:
     • The first array is the sales data: SalesTbl[Sales].
     • The second array is the numeric representation of whether the month is January: 1 for “Jan” and 0 for other months.
   • The multiplication results in:
     • The sales value when the month is January (SalesTbl[Sales] * 1).
     • 0 when the month is not January (SalesTbl[Sales] * 0).
   • Finally, SUMPRODUCT adds up all these results to give the total sales for January.

Sumproduct Function Example:

Result: 100+200=300100 + 200 = 300100+200=300.

Summary:

The formula calculates the sum of SalesTbl[Sales] for rows where the month in SalesTbl[Date] is January.

Want to join online or Recorded Class: click here