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.
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)
ExampleThe 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.
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 

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)
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
Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997  2004, F. David McRitchie, All Rights Reserved