• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / SUMPRODUCT Function to Count with Multiple Conditions

SUMPRODUCT Function to Count with Multiple Conditions

There are many ways to count with multiple conditions in Excel. These can range from using the COUNTIFS function, PivotTables, or by simply filtering the data. However, none of them are as flexible as the SUMPRODUCT function.

The SUMPRODUCT function is used to multiply the values of multiple arrays and then sum the totals. However, it is capable of so much more amazing feats. It truly is one of those hidden gems in Excel waiting to be discovered.

Using SUMPRODUCT to Count with Multiple Conditions

In this example, the SUMPRODUCT function is being used to count the number of sales based on multiple criteria. The following image, shows a snapshot of a sales list. The list has been formatted as a table named tblSales.

Sample data to count with multiple conditions in Excel

To use the SUMPRODUCT function to count records, we will enter the formula as below.

=SUMPRODUCT((condition 1)*(condition 2)*(condition 3))

Each condition, or array, is enclosed within its own set of brackets. The multiplication operator (*) is used to apply the AND logic between each condition.

If we required OR logic, the plus operator (+) would be used. We will see this shortly, but let’s begin with AND logic.

The following formula counts all the sales for the product entered in cell B3, and for the customer in cell C3.

=SUMPRODUCT((tblSales[Product]=B3)*(tblSales[Customer]=C3))
Count multiple conditions with AND logic

An area where SUMPRODUCT demonstrates its flexibility over alternatives such as COUNTIFS, is its ability to handle OR logic between conditions.

The following formula is used to count all the sales for the products in cell B3 and B4. The plus operator (+) has been used to create the OR logic between conditions. Notice each condition is within its own set of brackets again.

=SUMPRODUCT((tblSales[Product]=B3)+(tblSales[Product]=B4))
SUMPRODUCT function with OR logic between conditions

The SUMPRODUCT function can handle many more conditions if necessary. When you get to grips with it, you will feel like no data analysis is out of your reach.

Related Posts:

  • Count the Number of Words in a Cell
    Count the Number of Words in a Cell
  • SEQUENCE Function in Excel
    SEQUENCE Function in Excel
  • Multiple Condition Lookup Formula in Excel
    Multiple Condition Lookup Formula in Excel
  • Excel FILTER Function – The Best Function in Excel
    Excel FILTER Function – The Best Function in Excel

Reader Interactions

Comments

  1. Bashan G. Kinyunyu says

    9 January 2014 at 12:29 pm

    I thank you for a very helpful training you provide me since I joined your class through Internet. Go on Please help me and other fellows of this world the Mighty God bless you abundantly

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·