| Links | DSUM() | SUMPRODUCT() | Array functions and SUMIF() | |
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.
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.
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 |
Click here for details about obtaining this file |
file: xlCondSum.htm | Page last updated: Jan14 | © MeadInKent.co.uk 2015 | CMIDX S2 P8 N |