SUMIF Example

Using SUMIF to get Subtotals by Date

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)

Example 2, Loading Dock

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.

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

=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