How do I fix this Sumproduct formula?

[Duke92]

Hi All,

I am trying to get a sum total for ONLY unique Order Totals in Sheet 2, for Unique Name & Week combinations in Sheet 1. For example, for the Cell F3 in sheet 1, for all occurrences of John week 1 in sheet 1, I need to include ONLY the sum of maximum order values for Names in Week 1 from sheet 2 ONLY ONCE, in an aggregate sum of all other unique name/week combinations as in sheet 1.

My current formula in F3 as below gives the max of all unique values which is 7000. But the real answer should only select the maximum occurrence for John and add with peter and paul, so total should be 8200.

=SUMPRODUCT(INDEX(--((MAX(COUNTIF(A3:A9,Sheet2!A1:A5))) * (MAX(COUNTIF(B3:B9,Sheet2!B1:B5)))>0),0),MAX(Sheet2!C1:C5))

I should add that Cell F3 is the output cell where the sum should be returned. In this case it is the cell with 7000 in the first image

 

[Duke92]

This appears impossible with a single formula, this method looks like it works.

Book2
	A	B	C			F	G	H			K
1	Week	Name	Orders
2	1	John	5000								Unique Sum for all Unique Name/week combinations
3	1	Peter	200			Week	Name	Orders			10200
4	1	Paul	1000			1	John	5000
5	2	Peter	100			1	Peter	200
6	2	Paul	3000			1	Paul	1000
7	2	John	7000			2	John	7000
8	3	Paul	1500			2	Peter	100
9						2	Paul	3000
Sheet3

Cell Formulas
Range	Formula
K3	K3	=SUM(IFERROR(MAXIFS(H:H,G:G,$G$4:$G$9)/COUNTIF(G:G,$G$4:$G$9),0))
H4:H9	H4	=MAXIFS(C:C,B:B,G4,A:A,F4)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Hi Jason,

Thanks for your efforts. Your solution is the closest I have gotten to.

But just before I close, I wonder if you can assist to modify your formula below for another instance?

I see only the names column G are used as criteria to pull the order totals. Is it possible that this formula below can accept two criteria? Simply, is there the chance it can do the maxifs with both week & name conditions? so the max of each week and name. Sum (max john from week 1 + max john from week 2 + max peter from week 1 + max peter from week 2 ..) for as many names and weeks as is?

=SUM(IFERROR(MAXIFS(H:H,G:G,$G$4:$G$9)/COUNTIF(G:G,$G$4:$G$9),0))

I hope this is no trouble, otherwise I appreciate your help so far. Thanks

 

[Duke92]

Perhaps you already answered this when you said it will be impossible to use one formula, but I just wanted to confirm that you were saying this could not take two criteria as in my question above. If it can, then my question is totally answered.

 

[jasonb75]

If I'm reading that right, then that part can be done in one formula, it was isolating the max of all selected weeks that was not possible in one.

=SUM(MAXIFS(C:C,B:B,$G$4:$G$11,A:A,$F$4:$F$11))

 

[Duke92]

Hi Jason, this is the Maxif work-around I was looking for! Thanks
I have plugged it in and it works. Perhaps the only glitch is that the sum box in k2 below picks the max value twice. I've shown what the correct value should be below. I'm currently trying to divide the by number of occurrences but I'm not sure how this will work.
If you find something by any chance, this will be great, thanks! Thanks again, this has solved a lot haha

Maxifs.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1											Wrong	Correct
2											22200	15200
3	Week	Name				Week	Name	Orders
4	1	John				1	John	5000
5	2	Peter				1	Peter	200
6	2	Paul				1	Paul	1000
7	1	John				1	John	7000
8						2	John	9000
9						2	Peter	200
10						2	Paul	8000
11						2	John	15000
12
13
Sheet1

Cell Formulas
Range	Formula
K2	K2	=SUM(MAXIFS(H:H,G:G,Sheet1!$B$4:$B$9,F:F,Sheet1!$A$4:$A$9))
L2	L2	=H7+H9+H10
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

Not a glitch in the formula, John Week 1 appears in the criteria list twice so the result is included twice. Using formulas to filter results out of messy data is always going to be hit and miss.

This works with the sample above, but I haven't tested it beyond that.

=SUM(IFERROR(MAXIFS(H:H,G:G,Sheet1!$B$4:$B$9,F:F,Sheet1!$A$4:$A$9)/COUNTIFS(A:A,Sheet1!$A$4:$A$9,B:B,Sheet1!$B$4:$B$9),0))

 

[Duke92]

Not a glitch in the formula, John Week 1 appears in the criteria list twice so the result is included twice. Using formulas to filter results out of messy data is always going to be hit and miss.

This works with the sample above, but I haven't tested it beyond that.

=SUM(IFERROR(MAXIFS(H:H,G:G,Sheet1!$B$4:$B$9,F:F,Sheet1!$A$4:$A$9)/COUNTIFS(A:A,Sheet1!$A$4:$A$9,B:B,Sheet1!$B$4:$B$9),0))

Hi Jason, I can say that this problem is now solved. This formula was indeed the perfect fix. Really appreciate your time and help!