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

[Mtown91]

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!

 

[DRSteele]

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.

 

[Jonmo1]

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)

 

[Mtown91]

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!

 

[Mtown91]

DRSteele, thank you for your link - this will definitely help for future queries and help I can provide others!

 

[DRSteele]

I don't mean to interfere, but here is a super demonstration of powerful function SUMPRODUCT. Videos are so clear, and it might help save Jonmo1 from all that typing!

https://www.youtube.com/watch?v=IA6w2RIX2Qg
https://www.youtube.com/watch?v=vxpeEf8MYaY

 

[Mtown91]

DRSteele, thank you for these links! Watched the first one so far, how have I lived without this formula...

 

[Jonmo1]

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.