Sum the amount based on multiple selected criteria

[Huy Nguyen]

Hi guys

I have a worksheet with a dataset (about 100K -500K rows) exported from a system. I want to have a code that sums the total amount based on multiple criteria selected by user. . Attached is a sample of the dataset and an illustration of what I'm trying to do. I'm open to both formulas and VBA. Thanks guys.

 

[Somendra Misra]

Hi,

See the file. It's just an idea with the help of recorded macro. If you feel that this is something useful to you we can make it dynamic.

Sheet has two button.
RUN button is used to apply advance filter and get the sum.
Click in one cell in between data and press remove filter button to remove advance filter.

Regards,

 

[Huy Nguyen]

Hi Somendra Misra,

Thanks for your prompt response. I see what you're doing here. It sounds like a good idea, except I have to come up with a way to get the dynamic criteria range. What is your suggestion?

 

[Haseeb A]

Hello Huy,

Here is one way with formula. You can convert your data to table structure, then define dynamic formula to criteria range.

Then you can use SUMPRODUCT like,

=SUMPRODUCT(ISNUMBER(MATCH(Table1[Scenarios],Scenarios,0)+MATCH(Table1[Group],Group,0)+MATCH(Table1[Fiscal Period],Period,0))+0,Table1[Amount])

See attached.

 

[Somendra Misra]

Thanks @Haseeb A for giving the idea, so here's my approach on this:

=SUMPRODUCT(Table1[Amount],COUNTIF(Result!$A$2:$A$11,Table1[Scenarios])*COUNTIF(Result!$B$2:$B$11,Table1[Group])*COUNTIF(Result!$C$2:$C$11,Table1[Fiscal Period]))

Regards,

 

[Huy Nguyen]

Thanks @Somendra Misra and @Haseeb A. Both approaches do the trick. I can think of a way to generate the criteria table. Thanks again! You guys rock!!