< Go Back

Sum if one criteria multiple columns

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?

  1. 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.
  2. 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

  1. 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   

  1. 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

  1. 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

  1.  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.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar