Results 1 to 11 of 11
Thread: Sumif (2000)

20060601, 08:38 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Sumif (2000)
Tried searching this somewhat common topic, but to no avail: On worksheet A, I have listed purchases of a commodity in units by date of purchase. For example, the cell located in column A, at row 1 would contain a date, and column B, row 1 a quantity purchased. What I would like to do on worksheet B is to sum all of the purchases that occured prior to a certain date that would be set out on worksheet B. For example, assume I have entered 1/1/2006 in A1 of worksheet B. In B1 of that worksheet, I would like to sum all of the purchases made prior to the date in A1 that are separately set out on worksheet A. I know there's an easy answer, but my brain is just not at full speed yet!
Thanks in advance!
Jeff

20060601, 08:42 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Sumif (2000)
How about
<code>
=SUMIF('Worksheet A'!A1:A100,"<"&A1,'Worksheet A'!B1:B100)
</code>
Replace Worksheet A with the actual name of the sheet, and adjust the ranges as needed.

20060601, 08:44 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sumif (2000)
Something like this in WorksheetB, cell B1 perhaps:
=SUMIF(WorksheetA!A:A,"<"& A1,WorksheetA!B:[img]/forums/images/smilies/cool.gif[/img]
This will sum the values in WorksheetA col B, where the dates in WorksheetA col A are less than the value of A1.
Steve

20060601, 09:26 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sumif (2000)
Thanks Hans and Steve.

20060601, 21:15 #5
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sumif (2000)
Hans & Steve,
An afterthought: how come just using the sumif and Year() functions and arrays don't work? I tried, and it doesn't...

20060601, 21:18 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Sumif (2000)
We'd have to know what formula(s) you tried...

20060601, 21:36 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Sumif (2000)
In the first place, you cannot use the DATE function this way  the syntax for it is DATE(year,month,day), for example DATE(2006,7,4) returns the 4th of July in this year.
In the second place, WorksheetA(A1:A100) is not a valid way to refer to a range, it should be WorksheetA!A1:A100.
In the third place, SUMIF is an implicit array function, there is no need to have an array formula when you use SUMIF; the same goes for COUNTIF and SUMPRODUCT.

20060601, 21:37 #8
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sumif (2000)
Example: {=sumif(Date(worksheetA(A1:A100))<Date(worksheetB( A1),worksheetA(B1:B100))},
where, "worksheetA(A1:A100)" represent the dates of purchase from worksheet A; worksheetB(A1) represents the date before which I want all of the purchases from worksheetA to be summed, and workshrrtA(B1:B100) the purchases.

20060601, 21:44 #9
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sumif (2000)
Thanks, Hans...I suppose three strikes and you're OUT!!! LOL!!!
Thanks again!
P. S., what about YEAR() instead of DATE(), assuming my other syntax is correct?

20060601, 21:50 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Sumif (2000)
If you want to compare only the year, you could use the YEAR function, you could use the following. This *is* an array formula, i.e. it must be confirmed with Ctrl+Shift+Enter:
<code>
=SUM(IF(YEAR('Worksheet A'!A1:A100)<YEAR(A1),'Worksheet A'!B1:B100))
</code>
Note that this uses SUM combined with IF, not SUMIF. Another possibility, this time a nonarray formula again, is
<code>
=SUMPRODUCT((YEAR('Worksheet A'!A1:A100)<YEAR(A1))*'Worksheet A'!B1:B100)
</code>
It's not an array formula because SUMPRODUCT is an implicit array function.

20060601, 22:03 #11
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Sumif (2000)
As usual, Hans, PERFECTO!!!
Thanks again.