How To Sum Data With Multiple Vertical and Horizontal Criteria Using SUMPRODUCT Or SUMIFS In Excel

In this tutorial we are going to look at how to filter and sum up a column of data that has multiple criteria in both rows and columns using two options – either the SUMPRODUCT function or the SUMIFS function.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a data range of Names, Divisions and Months worth of data:

I have data validation drop down lists to select Name, Division and Month. Then I have two options for solutions for this query – one using SUMPRODUCT and the other using SUMIFS:

I used named ranges for Names, Divisions, Months, & Data to make creating and analyzing my formulas easier.

The formulas that were created to solve this query are:

SUMPRODUCT:

=SUMPRODUCT((Name=Q2)*(Division=Q3)*(Month=Q4),Data)

SUMIFS:

=SUMIFS(OFFSET(B1,1,MATCH(Q4,Month,0),COUNT(N:N),1),Name,Q2,Division,Q3)

Please take a few minutes to watch this video so you can see how we arrived at these solutions.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , ,

No comments yet.

Leave a Reply