In Excel, the SUMIF is a function that is used to add a range of cells satisfying criteria. Now to add multiple columns which satisfy criteria the SUMIF cannot be used since a SUMIF can handle only multiple rows and not multiple columns. Therefore, the built function called ‘SUMPRODUCT’ is used to perform a Sum if one criterion multiple columns.

The Excel worksheet makes use of the built-in SUMPRODUCT function to do a sum if one criterion multiple columns operation where this function multiplies a range or array of cells and returns the sum of products. The method of how to use this function as a sum if one criteria multiple columns are detailed in the further section.

**Generic Formula**

**=SUMPRODUCT((criteria_range)*(sum_range))**

**How does the formula work?**

- The actual syntax of a SUMPRODUCT function is ‘=SUMPRODUCT(array1,[array2,….arrayn])’ where
**array 1**is a parameter in which the first set of range of cells is multiplied and added. The parameter [**array 2 … arrayn]**are the second and onward range of cells multiplied and added which is optional. - The generic formula used above has only the
**array1**parameter and is used in such a way that it performs the sum if one criterion and multiple columns. The ‘criteria_range’ results in an array of TRUE or FALSE values (ex. {TRUE; FALSE; TRUE} ) and is then multiplied with the ‘sum_range’ which contains the values to be added.

**Example **

- Now let us try to apply the formula in an excel worksheet and understand them in steps. Let us consider a list of indoor games and calculate the total number of matches played on dates 21/9/2018, 22/9/2018, 23/9/2018.

* Figure 1. The table containing data *

- The function is used as shown in the screenshot below and here the ‘criteria’ is Chess for which the total matches played is calculated. This expression generates a true and false array i.e, {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE} where it returns TRUE for “Chess” and FALSE for anything else.

*Figure 2. Using the SUMPRODUCT function and applying the criteria generic parameter *

- The second expression contains the range of values to be summed up. The TRUE and FALSE values are multiplied with the values of the range i.e, {6,4,1;0,0,0;0,0,0;1,6,8;0,0,0;0,0,0;} where TRUE is treated as a ‘1’ and FALSE as ‘0’.

*Figure 3. Applying the sum_range generic parameter *

- The values are then summed up to give the result as ‘26’ as shown below which the total amount of Chess matches played.

*Figure 4. Result obtained and the total chess matches played is calculated successfully*

**Usage with a cell containing specific text**

- The SUMPRODUCT does not support wildcards(*,? etc.,) so, do to a ‘cell containing specific text’ type search where the criteria if satisfied partially also returns a TRUE value, to avoid this
**ISNUMBER**and**SEARCH**functions are used together as shown below.

`=SUMPRODUCT(ISNUMBER(SEARCH(substring,test)) * sum_range)`

- The ISNUMBER function returns a TRUE for numbers and FALSE for anything else. The SEARCH function returns the position of a search. The ‘substring’ represents the specific text you are looking for and ‘test’ represents the cell being tested.

## Leave a Comment