# SUMIF Example

Location:   http://www.mvps.org/dmcritchie/excel/sumif.htm
[View without Frames]

Using SUMIF to get Subtotals by Date

 A B C D E 1 Name Date Amount Amt/Date =GetFormula(Dnn) 2 a2 03/15/1998 1.00 =IF(B3<>B2,SUMIF(B\$2:B2,B2,C\$2:C2),"") 3 a3 03/15/1998 2.00 3.00 =IF(B4<>B3,SUMIF(B\$2:B3,B3,C\$2:C3),"") 4 a4 03/16/1998 4.00 =IF(B5<>B4,SUMIF(B\$2:B4,B4,C\$2:C4),"") 5 a5 03/16/1998 8.00 12.00 =IF(B6<>B5,SUMIF(B\$2:B5,B5,C\$2:C5),"") 6 a6 03/17/1998 16.00 =IF(B7<>B6,SUMIF(B\$2:B6,B6,C\$2:C6),"") 7 a7 03/17/1998 32.00 48.00 =IF(B8<>B7,SUMIF(B\$2:B7,B7,C\$2:C7),"") 8 a8 03/18/1998 64.00 64.00 =IF(B9<>B8,SUMIF(B\$2:B8,B8,C\$2:C8),"") 9 Total 127.00 127.00 =SUM(D1:D8)

Sum all the amounts per date using a solution provided by Bill Manville (Oxford, England) May 7, 1998 in the microsoft.public.excel.misc newsgroup.

caution:  In order for this example to work the dates must be sorted or arranged so that same dates appear next to one another; otherwise, you will get multiple totals with the later totals also incorporating previous totals for the same date.

Sum for a date range formula no table included:
=SUMIF(A1:A6,"<"&DATE(2007,4,3),B1:B6)-SUMIF(A1:A6,"<"&DATE(2007,4,1),B1:B6)

 A B C D E 1 Item Qty/order # of ## Total Qty 2 Sink 2 1 2 3 Hob 2 1 2 4 Desk 2 1 2 5 Desk 2 2 2 4 6 Oven 1 1 1 1 7 Hob 2 2 2 4 8 Floor 1 1 1 1 9 Paint 2 1 2 10 Paint 2 2 2 4 11 Sink 2 2 2 4

```Col A   is the Item
Col B   is the Qty (per order)
Col C   the   #   of   # of ## counts
Col d   the   ##  of   # of ## counts
Col e   total Qty of an item per truck

Code the following on line 2  (titles on row 1)
C2:  =Countif(\$A\$2:\$A2,\$A2)
D2:  =Countif(\$A:\$A,\$A2)
E2:  =IF(COUNTIF(\$A\$2:\$A2,\$A2)=COUNTIF(\$A:\$A,\$A2),
SUMIF(A\$2:A2,A2,B\$2:B2),"")
```
The formulas will work whether you sorted on Column A or not.
For instance if you loaded the truck based on destination, size, weight or other criteria.

## Example 3 -- include or exclude exceptions (#example3)

 F G 1 Qty Exceptions / Formula 2 1 3 33 x 4 23 x 5 7 6 63 7 1 8 68 9 77 10 73 11 346 =SUM(F2:F10) 12 13 56 =SUMIF(G2:G10,"x",F2:F10) 14 290 =SUMIF(G2:G10,"<>x",F2:F10)

Syntax   SUMIF(range, criteria, sum_range)

The SUMIF worksheet function will compare each value in the range to the criteria.  For each match, it will sum the corresponding cell in the sum_range.  If the first cell in the range matches the criteria, the first cell in the sum_range will be added and so on until there are no more cells in the range.  If the sum_range is omitted, the function sums each cell in the range which matches the criteria.

Range is the range of cells you want evaluated.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added.  For example, Criteria can be expressed as 32, "32", ">32", "apples".

Sum_range are the actual cells to sum.  The cells in sum_range are summed only if their corresponding cells in range match the criteria.  If Sum_range is omitted, the cells in range are summed.

Example
Suppose A1:A4 contains the following property values for four homes: \$100000, \$200000, \$300000, \$400000, respectively. B1:B4 contains the following sales commissions on each of the corresponding property values: \$7000, \$14000, \$21000, \$28000.

SUMIF(A1:A4,">160000",B1:B4) equals \$63000

AB
1100,0007,000
2200,00014,000
3300,00021,000
4400,00028,000

Another SUMIF Example

 A B C D E 1 1 2 4 8 sum_range 2 5 10 15 10 3 0 1 1 0 range 4 0 1 1 0 5 SUMIF(range, criteria, sum_range) 6 31 =SUMIF(A3:D4,1,A1:D2) 7 24 =SUMIF(A3:D4,0,A1:D2) 8 4 =SUMIF(A3:D4,1)

### Some quick items

Q: How to get average of a column by excluding rows with zeros. 1998-03-21
=SUMIF(Area,">0")/COUNTIF(Area,">0") or, for a column: =SUMIF(A:A,">0")/COUNTIF(A:A,">0")
Q: How to use INDIRECT to refer to another worksheet. 2001-07-13
=SUMIF(INDIRECT("'"&\$B\$1&"'!A:A"),A5,INDIRECT("'"&\$B\$1&"'!D:D"))

### Summing by date ranges using Arrays

Suppose you have dates in Column A spanning a few months in A3:A25, and you want to find the Parts quantity included in Column D for each month.

Using named ranges defined within Column A and Column D.

```=SUM((MONTH(data_A)=4)*data_d)
array formula    Ctrl+Shift+Enter

or better including the year (first with a named range then without)
=SUM((MONTH(data_A)=4)*(YEAR(Data_A)=2001)*Data_D)
=SUM((MONTH(Sheet1!A\$3:A\$25)=4)*(YEAR(Sheet1!A\$3:A\$250)=2001)*Sheet1!D\$3:D\$25)
=SUM((MONTH(Sheet1!A\$3:A\$250)=4)*(YEAR(Sheet1!A\$3:A\$250)=2001)*Sheet1!D\$3:D\$250)
array formula    Ctrl+Shift+Enter
```

### Related material on other websites

You are one of many distinguished visitors who have visited my site here or in a previous location  since May 07, 1998.

Please send your comments concerning this web page to: David McRitchie send email comments