Excel Formula Help – SUMIF with Operators
This blog follows on an earlier blog covering the use of SUMIF, using the same data. Here, we will show how to use SUMIF with Operators and cell references in Microsoft Excel.
If you read the earlier blogs you will remember that we set up Range Names to make it easier to work with data. If you need to remind yourself about what Range Names are and how we use them, click on the link above for a quick review; at the end of that blog our spreadsheet should have looked like this:
For this exercise, we have set up a third field where we would like to show the sum of the invoice amounts for each calender quarter of the year. As you can see we have entered a formula that tries to use a 'less than or equal to' operator next to the adjacent cell reference in the format <=E12 .
With E12 representing all the days in Q1 of the calender year, the SUMIF formula attempts to find the sum of the amounts for all invoices paid on days within that time period. However, as you can see, we have a problem.
This occurs because, on this occasion, we need to enclose our <= to operators in quotation marks and include an ampersand between the operator and the cell reference, such as "<="&E12
Now if we hit Return we will get the total for Q1 we are looking for. We can then copy this down and get the sum cumulative totals for quarters through out the year:
However, we in fact want the sum total of each individual quarter, not cumulative totals (which makes our Grand total incorrect). To do this we need to select the second total in E13 (representing Q2, our second calender quarter) and edit the formula again so that it reads: =SUMIF( _date,"<="&E13, _amount)-SUM($F$12:F12) Here, we are telling the formula to subtract any amounts that come before it in column E.
Note how we have edited the formula in the formula bar. In doing so, we have selected the first F12 and hit function key F4 to convert to an absolute reference.
We can then copy the formula down to receive individual totals for each quarter, with our Grand Total now adding up correctly.
For more hold using SUMIF and other functions with Operators, give Excel4Business a call. You can also learn more from Microsoft's help pages here.
