Results 1 to 11 of 11

Thread: Sumif (2000)

  1. #1
    Bronze Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sumif (2000)

    Thanks Hans and Steve.

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sumif (2000)

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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

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

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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 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. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
  •