Using SUMIFS with arrays | Excel problem... Solved! | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ Get the example file ★

★ About this video ★
Have you ever put a dynamic array formula inside SUMIFS... of course you have. Or else why would you be here?

Well, let's solve this problem... how can we use SUMIFS with arrays???

0:00 Introduction
0:32 The problem
4:01 Using range functions
6:35 FILTER/SUM
8:10 SUMIFS Spill Range
10:45 GROUPBY
12:51 Conclusion

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[Jim-zmfw]

Thank you, Mark. Truly awesome. And thank you for your clear presentation style in all your videos.

[ziggle]

Excellent! I am excited for when my update channel gets GROUPBY. Thanks, Mark!

[leandratel]

I had this video on my watchlist for quite a while. Today I finally took the time to watch it once and will spend the rest of the month watching it repeatedly until I really understand and be able to reproduce what you're presenting. Good stuff, thanks 👍🏼

[alexkerr]

Fantastic. I've been struggling with this and not finding useful explanations anywhere. Also, I didn't know about '0' in the index formula 🥳 Thank you.

[rpopecpa]

Brilliant! Thank you for this solution. I have run into this problem many times.

[DataVisualisation]

Great video, well explained where the problem is and how different ways could look like.

[huseyinburaktasci]

Thank you, Mark! It is a great video and a very clear explanation. The content becomes even more advanced by the end of the video. 😅 As a big fan of Sumifs function, I learned a lot!

[hsilva]

Great tip and very well explained, thanks

[martyc]

Brilliant video Mark. It gets confusing sometimes figuring out what works with what in the transition to dynamic arrays last few years. I usually figure it out but takes a few attempts! Group/pivotby will be great.

[tuzaguilar]

Great presentation. Very clear. Please continue bringing these types of tutorials. Thank you. and subscribed!

[jimfitch]

Thank you! I use dynamic array functions extensively (almost always within LET statements for readability & documentation) & have been frustrated for a long time by the inability to use SUMIFS, et al, with DA functions. I never understood until viewing this video that (virtual) ranges & arrays are different beasts. The “bug” makes sense to me now. I still wish that MS would fix this “bug”, but I’ll labor on without the same level of frustration. Thanks!

[tushev]

Excellent explanation! Thank you for your efforts! 🎁🎁🎁

[Fabi_terra]

Incredible! Thanks so much, Mark! Great tutorials, elegant and clear presentation! Congratulations!

[IvanCortinas_ES]

Excellent high level explanation. Without a doubt, the introduction of GROUPBY has avoided using the BYROW - LAMBDA combination (always complicated for a user with little practice). Thank you Mark.

[HusseinKorish]

This is Amazing ... I already had these problems with sumifs ... now i know ... Many Thanks

[chiengsaby]

Thanks. I was able to apply this in one of the spreadsheet tools I made. Well explained and really helpful.

[goldenboy]

The video didn't address my original issue, but I did learn what VSTACK is which helped me accomplish what I originally set out to solve, so thank you!

[safuwanch]

🎉🎉🎉🎉i was encounter this problem, and i used sum filter, know i know more alternative thanks ❤

[yazazan]

Wow! Fantastic job. Thank you very much, good sir.

[mohammedelsakally]

Thank you Mark for your always valuable info and tricks you are providing to us...