| | 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'). Note that SUMIF is faster than SUMPRODUCT.
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 |