meadinkent website
| Links | DSUM() | SUMPRODUCT() | Array functions and SUMIF() |

A choice of Excel functions for conditional sums

If you want to add up certain values within a range of numbers which meet a criteria, this is called a conditional sum. Excel contains at least four distinct functions and methods for acheiving this. This page outlines the choices and each function is shown in detail elsewhere on this website.

conditional sum

SUMIF()   simple single criteria, wildcards permitted
  16 =SUMIF(B4:B8,"<>D*",C4)
    sum values where code does not start with D
DSUM()   complex AND / OR criteria, wildcards permitted, criteria range (E3:F5) required
  12 =DSUM(A3:C8,3,E3:F5)
    sum where colour is red AND code does not start with A OR any colour and code starts with B
SUMPRODUCT()   multiple criteria, no wildcards, functions can be applied to range
  9 =SUMPRODUCT((A4:A8="RED")*(C4:C8>3)*(C4:C8))
    sum values where colour is red AND value is greater than 3
  7 =SUMPRODUCT((ISERR(FIND("A",B4:B8,1))=FALSE)*(C4:C8>4)*(C4:C8))
    sum where code contains A and value is greater than 4
SUM() arrays    multiple criteria, no wildcards, functions can be applied to range (incl Logical)
  9 {=SUM((A4:A8="RED")*(C4:C8>3)*(C4:C8))}
    sum values where colour is red AND value is greater than 3
  9 {=SUM(IF(ISERR(FIND("A",B4:B8,1))=FALSE,C4:C8,0))}
    sum values where code contains A

SUMIF() is a simple solution if you only have a single criteria. DSUM() may be most useful if you are expecting the user to change some of the criteria - although this can also be acheived with the other functions by using cell references instead of fixed variables (i.e. =H4 instead of ='blue').

SUMPRODUCT() and SUM() with array formulas are very similar and almost interchangeable. They can't handle wild card characters (* ?) but this can be replaced using FIND() or SEARCH(). SUMPRODUCT() may require less PC memory and SUM() arrays require the user to enter them using <Ctrl>+<Shift>+<Enter>. The array formulas however can be programmed to handle errors within a range of values.

conditional sum calculation

The error in cell C14 will corrupt most sum calculations which are referring to the range C12:C16. An array formula can avoid the error if the ISERROR function is contained within a logical IF function. This cannot be done within SUMPRODUCT().


SUMPRODUCT() #DIV/0! =SUMPRODUCT((A12:A16="blue")*(C12:C16))
  #DIV/0! =SUMPRODUCT((ISERROR(C12:C16)=FALSE)*(A12:A16="blue")*(C12:C16))
SUM() arrays #VALUE! ={SUM((A12:A16="blue")*(ISERROR(C12:C16)=FALSE)*(C12:C16))}
  10 ={SUM(IF(ISERROR(C12:C16)=TRUE,0,C12:C16)*(A12:A16="blue"))}

This page is not included in
the Excel Functions guide
PDF Click here for details about
obtaining this file

file: xlCondSum.htm Page last updated: Jan14 Open MeadInKent Facebook page MeadInKent.co.uk 2015 CMIDX S2 P8 N