SUMPRODUCT Function an Alternative to SUMIFS

Excel’s SUMPRODUCT function for Excel 2003 users who require the SUMIFS, COUNTIFS or AVERAGEIFS functions (the *IFS series of functions).found in office 2007 onwards. Excel 2007 or 2010 users keep we use SUMPRODUCT which gets around the limitations of  the Excel’s *IFS series of functions. Limitations using the *IFS functions: The *IFS functions works only with the AND logic between the criteria. Example: SUMIFS(sum the range A if range B = criteria 1, AND range C = criteria 2, AND range D = criteria 3…..) However using  SUMPRODUCT we can utilise the OR logic as well as the AND logic in a SUMIF style of calculation. Example: SUMPRODUCT(range A, if range B = criteria 1, OR range B = criteria 2, AND range C = criteria 3…..) Note: the configuration of the SUMPRODUCT formula above is for illustration purposes only, the actual syntax of the SUMPRODUCT function is different. See below. The Excel SUMPRODUCT Function using examples The data below is laid out in a table that has been converted to a range. Each column has a named range the same as in the header row 1. In my examples I will refer to the column range G2:G207 as the named range ‘Applied’. Using the SUMPRODUCT function Instead of SUMIF Using the data above let’s say I want to sum the Volume for the Endrulf solar system. My SUMPRODUCT formula would look like this: =SUMPRODUCT((Volume)*(solarSystem=”Endrulf”)) How to use the SUMPRODUCT function Instead of SUMIFs You are required to sum the volume for Endrulf solar system AND IF Jumps = 6 =SUMPRODUCT((Volume)*((solarSystem=”Endrulf”)*(jumps=6))) How to use the SUMPRODUCT to SUMIF with OR as well as AND logic =SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6)) How the  formula reads: =SUM ((Volume) IF ((solarSystem=”Rens”) OR (solarSystem=”Endrulf”)) AND (jumps=6)) In SUMPRODUCT functions you can employ the AND logic, and OR logic using the * and + symbol: When the multiplication symbol * is used it reads ‘AND’. When the plus symbol + is used it reads ‘OR’. How the SUMPRODUCT Function Works In the SUMPRODUCT function Excel is testing for TRUE or FALSE answers, and in Excel the numeric equivalent for TRUE is 1, and for FALSE it is 0. These are standard Boolean terms…. So, using this formula: =SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6)) Let’s look at our data and take row 4 below as an example and apply the formula: = 13,417 * 1 * 0 = 0 Function Explained: SUM Volume 13,417 * 1 (because G4=Rens therefore = TRUE, which = 1) * 0 (because H4=7 therefore = FALSE which = 0) Or if we look at row 6: 5,217,955 * 1 * 1 = 5,217,955 How to use the SUMPRODUCT to COUNT with multiple criterion Using the example; if you want to use SUMPRODUCT to count values based on multiple criterion using AND or OR, you would simply drop the Volume component of the formula like this: =SUMPRODUCT(((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6)) How to use SUMPRODUCT to AVERAGE with multiple criterion To calculate the AVERAGE we simply divide the total amount by the COUNT of the total volume like this: =SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6)) / SUMPRODUCT(((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(jumps=6)) SUMPRODUCT Using Dates as Criteria Now we shall add the criteria for the month of January 2011 instead of the ‘jumps=6’: =SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(Date>=DATEVALUE(“01/01/2011″)*(Date<=DATEVALUE(“31/01/2011″)))) Remember the Date could also refer to a cell that contained the date, or the date serial number: Using cell references for the date (cell L12 contains 1/1/2011 and cell M12 contains 31/1/2011): =SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(Date>=L12)*(Date<=M12)) Using serial numbers for the date: =SUMPRODUCT((Volume)*((solarSystem=”Rens”)+(solarSystem=”Endrulf”))*(Date>=40544)*(Date<=40574)) SUMPRODUCT as an Alternative to Helper Columns What say we wanted to know the sum of the Volume x Price. We could insert a formula in column J that calculated Price x Volume for each row of data, and then sum column J to get a total, or we could use the SUMPRODUCT function like this: =SUMPRODUCT(price,Volume) Remember: ‘price’ is the named range for column A and ‘Volume’ is the named range for column D. The advantage of the SUMPRODUCT of using  this calculation is that you can achieve the same result in one cell that would otherwise take up whole columns. Recap the Rules In SUMPRODUCT functions you can use  the AND logic, and OR logic using the * and + symbol: When the multiplication symbol * is used it reads ‘AND’. When the plus symbol + is used it reads ‘OR’. Tip: if your formula results in a zero and you know it should be >zero then you either have an error in your data, or you have an error in your formula.

Excel can be fun try out some of its features Detailed on this Site

Mike Barrett (MCT) Senior Training Advisor