Replacement for SUMIF

[Ecel Dumbo]

HI All,

I am currently using a sumproduct formula
SUMPRODUCT(($B5=Category)*($A$11=GL_Account)*(AC$2=Date)*(Amount))

When used in a file having massive data, the calculation is slowing down. Is there any way i can replace this with , say for example, INDEX MATCH formula which can speed up the calculation

Tried using Name List , as shown in the formula, Category, GL_Account,Amount, still not helping

Not sure if i attach a Sample file, it would show my problem. Sample file will have less data, then using sumproduct would not have any issues

 

[Asheesh]

Hi,

Attaching a sample file mostly yeilds you quick and accurate results..

Try removing the defined names...and use tables..then replace sumproduct with sumifs
SUMifs( Amount,Category,$b5,GL_Account,$a$11,Date,AC$2)

Since, I am on phone so please ignore any typo

 

[NARAYANK991]

Hi ,

See the comment by Charles Williams in this link :

http://stackoverflow.com/questions/28745964/optimizing-excel-formulas-sumproduct-vs-sumifs-countifs

Narayan

 

[jeffreyweir]

Is there any reason you can't use a PivotTable to do this? That would fix the issue completely.

 

[jeffreyweir]

I've added a comprehensive answer to that StackOverflow link that draws on content from my book.