1. ## 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!
Jeff

2. ## Re: Sumif (2000)

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

3. ## 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

4. ## Re: Sumif (2000)

Thanks Hans and Steve.

5. ## 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...

6. ## Re: Sumif (2000)

We'd have to know what formula(s) you tried...

7. ## 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.

8. ## 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.

9. ## 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?

10. ## 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 non-array 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.

11. ## Re: Sumif (2000)

As usual, Hans, PERFECTO!!!
Thanks again.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•