Adding Mulitple Criteria in a Single Column

Creating a sum where the criteria is in the same column has usually been about creating a sumif or sumifs and adding multiple iterations together.  This turns formula which intuitively should be short into long, difficult to read formulations.  With the intelligent use of Sumproduct you can use one formula to capture multiple criteria in the same column.  Here is an example.

This is the formula to sum the items I have coloured.




The use of the plus sign (+) in the middle of the formula joins the criteria.  However, as with most formula on my site I always try and improve it.  Here is a more slim line version of the same formulation.




The use of the curly brackets { } allows me to put multiple criteria within these brackets.  There are numerous examples in the site proper of this technique.  The above example has 2 criteria but as long as these criteria are hard coded you can include as many as you wish separated by a comma.   


I will include an Excel file to show how this formulation flows.  It is always easier to see this in a practical setting.