How to sum based on column and row criteria in Excel?
I have a range of data which contains row and column headers, now, I want to take a sum of the cells that meet both column and row header criteria. For example, to sum the cells which column criteria is Tom and the row criteria is Feb as following screenshot shown. This article, I will talk about some useful formulas to solve it.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Here, you can apply the following formulas to sum the cells based on both the column and row criteria, please do as this:
Enter any one of the below formulas into a blank cell where you want to output the result:
And then press Shift + Ctrl + Enter keys together to get the result, see screenshot:
Note: In the above formulas: Tom and Feb are the column and row criteria that based on, A2:A7, B1:J1 are the column headers and row headers contain the criteria, B2:J7 is the data range that you want to sum.
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 6 months agohow would you do this same formula if you wanted to sum both Feb and March together? please help! thanks
To post as a guest, your comment is unpublished.· 1 years agoBrilliant
To post as a guest, your comment is unpublished.· 1 years agoWorth pointing out that of the two formulas provided above you do not need to enter the SUMPRODUCT formula with Ctrl + Shift + Enter. It will work perfectly well without it.
To post as a guest, your comment is unpublished.· 2 years agoAwesome, this is the one what i was looking for. thanks for the help