Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Dec 2001
    Location
    Br. Columbia, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sumif or VBA? (Excel 97)

    I have a simple sheet which has data in the following columns: (A) Date ([img]/forums/images/smilies/cool.gif[/img] Product Amount (D) Last Week. I need to populate each row of the Last Week column with the Amount that corresponds to the same Product 7 days ago. I have tried different combinations of SumIf to no avail because the criteria has to match the 7 day condition and the same product. I also tried Dsum without success.
    Am I off course with SumIf, is there another function that I should be using or is there a VBA solution?

  2. #2
    Lounger
    Join Date
    Dec 2001
    Location
    Br. Columbia, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif or VBA? (Excel 97)

    Thank you Steve, that worked great.

    Are there any special tricks in entering array formulas in a macro?

    Is it possible to nest an OR function into this formula so that the sum of the data from 7 or 14 or 21 days is gathered?

  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 or VBA? (Excel 97)

    Try this ARRAY formula (confirm with ctrl-shift-enter, not enter):
    =sum(IF(($A$2:$A$100=(A2-7))*($B$2:$B$100=B2),$C$2:$C$100))

    Enter it into D2 and copy it down. If your range is named or larger than row 100 change accordingly

    Steve

  4. #4
    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 or VBA? (Excel 97)

    If I were doing a macro I wouldn't even use the array formula, I would just loop through and look at the criterion and enter the calculated values. I assume that you don't continue adding date from previous dates after a certain time so the values shouldn't change once they are calculated

    If you do enter the formula in a macro make sure you use formulaarray property not formula and should be RC nomenclature: (to get relative to the row you are on)
    <pre> Range("d2").FormulaArray = _
    "=SUM(IF((R2C1:R100C1=(R[1]C[-9]-7))*(R2C2:R100C2=R[1]C[-8]),R2C3:R100C3))"
    </pre>


    At some point (when the spreadsheet starts getting sluggish) you might consider PasteSpecial - Values the numbers. These can get pretty memory intensive to continue calculation

    To add ORs use a "+" instead of a "*" (which is for AND). Something like (all one line) in D2 and copy it down:

    =SUM(IF((($A$2:$A$100=(A2-7))+($A$2:$A$100=(A2-14))+($A$2:$A$100=(A2-21)))*($B$2:$B$100=B2),$C$2:$C$100))

    This is the SUM of all occurences of Product in B2, where the date is either 7, 14, or 21 days earlier than the date in A2, Though if you are going to check multiple ones, you might want to get the average value:
    =average(IF((($A$2:$A$100=(A2-7))+($A$2:$A$100=(A2-14))+($A$2:$A$100=(A2-21)))*($B$2:$B$100=B2),$C$2:$C$100))

    Other stat functions can also be used (min, Max, StdDev, Count, etc)
    Steve

  5. #5
    Lounger
    Join Date
    Dec 2001
    Location
    Br. Columbia, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif or VBA? (Excel 97)

    Steve,
    Thank you for this dazzling solution. Dare I ask how the loop would work? Would you use the array function in VBA?

    Mike.

  6. #6
    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 or VBA? (Excel 97)

    No I would NOT use the formula in VB.
    I would do what the formula would do:
    I would start with date (assuming less hits) and search the list of dates one by one to find the dates that match the criterion.
    Whenever I had a hit, I would check to see if the product also matched.
    If BOTH are hits I would add the amount into a variable. (if you are looking for min/max/avg, std dev, you will have to keep other variables for Count, variable-squared, current min, current max etc, and do some other coding)
    You just continue through the list of dates.
    Once you are through the list, the Variable will contain the SUM of all the matching dates and products. Enter it into the first D-cell and then go to the next one and continue.

    Steve

Posting Permissions

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