Array/Sumifs Alternatives?

**bchilme** · 12-09-2014, 05:33 PM

Hey all!

Got a large workbook that tracks certain activities throughout the entire year. Each day is broken down into 4 columns, of which 3 require formulas to calculate the total of a certain type of activity for that day for a certain person. I have tried two formulas to try to get what i want but once i use them for the entire year the workbook becomes unusable. The first one utilizes array formulas so that quickly becomes unusable after pasting it to only a few days. The second formula becomes unusable after pasting to a couple months. The SUMIFS uses the actual dataset and the array formulas uses pivot tables on a separate sheet to lookup values. The calendar will have between 20-80 rows depending on how many people are active. So 3 columns x 365 days x 80 people= 87000 cells that need formulas..... can anyone think of formulas that could be utilized that would still allow the workbook to be usable? I could add helped columns to the dataset if that assists.

{=IFERROR(INDEX('Production Pivot Tables'!$A$5:$C$5959,MATCH(1,('Production Pivot Tables'!B:B=B8)*('Production Pivot Tables'!A:A=J$7),0),3),0)}

=SUMIFS('Trending Log Import'!$AA:$AA,'Trending Log Import'!$T:$T,"*Task*",'Trending Log Import'!$N:$N,$A8,'Trending Log Import'!$C:$C,'2015 Central Region Production'!L$7)

Thanks and let me know if this doesnt make sense.

**mikeTRON** · 12-09-2014, 06:42 PM

Why not build a pivot table off the data, to aggregate it to the level you need, then use Index+Match?
Arrays and Sumifs are slick if you have a small dataset, otherwise I process the data before doing any reporting on it.

**bchilme** · 12-10-2014, 11:09 AM

Hi Mike! Thanks for the reply.

So i currently do have the data set up in pivot tables for when i use the Index and Match formula. Though i have multiple criteria to look for, name and date to pull back the values. I cant really picture how else to use index and match with a pivot table without using arrays when i have two criteria im looking for. Do you have a quick example i could starting using to help me picture it?

Thanks again!