sum the numbers under criteria

[Yjmmay34]

Hi, all . I need help with this problem. I want to sum the Qty if the Geo, Year, and Mon are same. then it should only the sum quantity left in the worksheet. Just like the sample i attached, please refer to it, which will be easier to understand.
Thank you!

[geekgirlau]

=SUMPRODUCT(($D$3:$D$22=M3)*($E$3:$E$22=N3)*($F$3:$F$22=O3)*$G$3:$G$22)

[Yjmmay34]

Hi, geek. i need a macro code to run this result. Because i need to insert this macro to my whole macro program. Please help. Thank you!

[geekgirlau]

That's the formula that will calculate the result you want, but you haven't explained what you're attempting to do. Does the sample represent the structure of the file? How will the macro be triggered? What cells need to be updated? If the formula exists, why do you need a macro?

Please post the code you have so far.

[Yjmmay34]

Hi, geek. Thank you for youe attention. I just need a macro to sum the number based on the condition. after sum those number, there is another macro to extract the sum number.

[geekgirlau]

Where are your criteria ranges going to be? These are currently in columns M, N & O in your sample.
Will these be static, or do you need to calculate these from your data range?

[Yjmmay34]

yes, geek. it will the static. i want to sum the number under same Geo, year, and month. then the sum number overwrite original data.

[geekgirlau]

Assuming you have named ranges:


Sheets("MySheet").Range("ResultRange").FormulaR1C1 = _
"=SUMPRODUCT((YearRange=RC[-3])*(QtrRange=RC[-2])*(MonthRange=RC[-1])*QtyRange)