Results 1 to 6 of 6
Thread: sumif or VBA? (Excel 97)

20030121, 18:49 #1
 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?

20030121, 19:25 #2
 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?

20030121, 20:05 #3
 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 ctrlshiftenter, not enter):
=sum(IF(($A$2:$A$100=(A27))*($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

20030121, 20:16 #4
 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=(A27))+($A$2:$A$100=(A214))+($A$2:$A$100=(A221)))*($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=(A27))+($A$2:$A$100=(A214))+($A$2:$A$100=(A221)))*($B$2:$B$100=B2),$C$2:$C$100))
Other stat functions can also be used (min, Max, StdDev, Count, etc)
Steve

20030121, 21:32 #5
 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.

20030122, 13:08 #6
 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, variablesquared, 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 Dcell and then go to the next one and continue.
Steve