# Thread: sumif or VBA? (Excel 97)

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