Multiple Criteria from Multiple Columns - Any Combination Match

[TheMacroNoob]

Hello excel experts,

I am trying to do something very frustrating. I have a table and I am trying to sum any combination of criteria in two columns in particular. A SUMIFS doesn't work for reasons I can't quite verbalize.
Is there a workaround with SUMPRODUCT or something similar to SUM based on this:

Book2
	B	C	D	E	F	G	H	I	J	K	L
5
6		Any combination of Divison AND Region. So A Egypt, A USA, B USA, B Egypt should sum.
7
8		Division	Region	Month	Year	Profit		Division	A	B
9		A	USA	12	2016	50		Region	USA	Egypt
10		B	CANADA	12	2016	50		Month	12
11		C	USA	12	2016	50		Year	2016
12		B	USA	12	2016	50
13		C	USA	12	2016	50		Total:	50
14		A	CANADA	12	2016	50		Intended:	200
15		A	EGYPT	12	2016	50
16		A	EGPYT	12	2016	50
17
Sheet1

Cell Formulas
Range	Formula
J13	J13	=SUM(SUMIFS(G9:G16,C9:C16,J8:K8,D9:D16,J9:K9,E9:E16,J10,F9:F16,J11))

 

[TheMacroNoob]

Hello excel experts,

I am trying to do something very frustrating. I have a table and I am trying to sum any combination of criteria in two columns in particular. A SUMIFS doesn't work for reasons I can't quite verbalize.
Is there a workaround with SUMPRODUCT or something similar to SUM based on this:

Book2
	B	C	D	E	F	G	H	I	J	K	L
5
6		Any combination of Divison AND Region. So A Egypt, A USA, B USA, B Egypt should sum.
7
8		Division	Region	Month	Year	Profit		Division	A	B
9		A	USA	12	2016	50		Region	USA	Egypt
10		B	CANADA	12	2016	50		Month	12
11		C	USA	12	2016	50		Year	2016
12		B	USA	12	2016	50
13		C	USA	12	2016	50		Total:	50
14		A	CANADA	12	2016	50		Intended:	200
15		A	EGYPT	12	2016	50
16		A	EGPYT	12	2016	50
17
Sheet1

Cell Formulas
Range	Formula
J13	J13	=SUM(SUMIFS(G9:G16,C9:C16,J8:K8,D9:D16,J9:K9,E9:E16,J10,F9:F16,J11))

Apparently the best way to solve a problem in Excel is to post it to a forum.... To then solve it yourself...

The solution was the following formula:
=SUMPRODUCT(--(ISNUMBER(XMATCH(C9:C16,J8:K8))),--(ISNUMBER(XMATCH(D9:D16,J9:K9))),--(ISNUMBER(XMATCH(E9:E16,J10))),--(ISNUMBER(XMATCH(F9:F16,J11))),G9:G16)

Wow. Carry on.

 

[Peter_SSs]

The solution was the following formula:
=SUMPRODUCT(--(ISNUMBER(XMATCH(C9:C16,J8:K8))),--(ISNUMBER(XMATCH(D9:D16,J9:K9))),--(ISNUMBER(XMATCH(E9:E16,J10))),--(ISNUMBER(XMATCH(F9:F16,J11))),G9:G16)

Tricky checking your formula because it actually returns 150 for the sample data not 200. However, that is because of the assumed typo in D16 which is EGPYT not EGYPT

A considerably shorter formula would be
=SUM(FILTER(G9:G16,ISNUMBER(MATCH(C9:C16,J8:K8,0)*MATCH(D9:D16,J9:K9,0))*(E9:E16=J10)*(F9:F16=J11),0))

 

[TheMacroNoob]

Tricky checking your formula because it actually returns 150 for the sample data not 200. However, that is because of the assumed typo in D16 which is EGPYT not EGYPT

A considerably shorter formula would be
=SUM(FILTER(G9:G16,ISNUMBER(MATCH(C9:C16,J8:K8,0)*MATCH(D9:D16,J9:K9,0))*(E9:E16=J10)*(F9:F16=J11),0))

Ha, you got me! I didn't spot the typo until I started seriously working on the problem.

Your formula is much more readable and understandable... I think I overcomplicated this issue quite a bit.

I marked your answer as THE answer and I will make use of that logic for sure.

Thank you!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.