Sumifs function not working on multiple axis ranges - help please!

Mtown91

New Member
Joined
Feb 12, 2016
Messages
4
Hi there,

Just trying to figure out why my sumifs formula is not working on a workseet of mine.

I'm trying to get a sum of a range of numbers based on a certain date against two different criteria.

A B C D E F
1 01/06/16 01/06/16 01/07/17 01/07/17
2 Truth Total Revenue 1000 1000 2000 2000
3 Bold Total Revenue 500 500 200 200
4 Truth Total Profit 100 100 100 100
5
6 01/06/16
SUMIF(C2:F4,A1:A4,A2,B1:B4,B2,C1:F1,A6)

Hopefully this makes sense?

Not sure how to attach an XL file, sorry!

Any help advice would be much appreciated!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Welcome to the forum. You can follow the link in my signature below to attach sample HTML data of your spreadsheet.

Also, please specify what you wish to accomplish; what you've provided has four columns of data and two of them are duplicates so I can't determine what you wish to sum.
 
Last edited:
Upvote 0
Welcome to the board.

Yeah, you can't do that with sumifs. All the ranges must be of similar dimensions.

Try
=SUMPRODUCT((A2:A4=A2)*(B2:B4=B2)*(C1:F1=A6)*(C2:F4)
 
Upvote 0
Jonmo1, you are brilliant! Thank you, been thinking about this all evening as well.

I didn't think to use Sumproduct as I never really understood the function - thought it was to multiply numbers.

Would you mind kindly explaining the logic behind the formula?

Thanks again!
 
Upvote 0
DRSteele, thank you for your link - this will definitely help for future queries and help I can provide others!
 
Upvote 0
DRSteele, thank you for these links! Watched the first one so far, how have I lived without this formula...
 
Upvote 0
You're welcome.

It's basically creating arrays of True/False results.
And when a math operation like * is done on a True or False value, True=1 False=0
So The 1's and 0's from those criteria arays are multiplied against the values in the sum range C2:F4
And those results are then summed.

DRSteel's links should help explain it too.
 
Upvote 0

Forum statistics

Threads
1,224,454
Messages
6,178,766
Members
452,875
Latest member
Disastrouscoder

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top