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

 

[jasonb75]

You need 1 formula for the max of each name, then sum them all separately.

If you want to do it with a single formula then you will need a version of excel that has the MAXIFS function (2019 or newer).

 

[Duke92]

You need 1 formula for the max of each name, then sum them all separately.

If you want to do it with a single formula then you will need a version of excel that has the MAXIFS function (2019 or newer).

Hi Jason, thanks for your response. I have the MAXIFs function but I/m not sure how to go about this

 

[jasonb75]

See if this helps, the '1' at the end of the formula denotes the week number.

Book2
	A	B	C	D	E	F
2	1	John	5000			Peter
3	1	Peter	200			John
4	1	Paul	1000			Paul
5	1	John	7000			8200
Sheet3

Cell Formulas
Range	Formula
F5	F5	=SUM(MAXIFS(C:C,B:B,$F$2:$F$4,A:A,1))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Duke92]

See if this helps, the '1' at the end of the formula denotes the week number.

Book2
	A	B	C	D	E	F
2	1	John	5000			Peter
3	1	Peter	200			John
4	1	Paul	1000			Paul
5	1	John	7000			8200
Sheet3

Cell Formulas
Range	Formula
F5	F5	=SUM(MAXIFS(C:C,B:B,$F$2:$F$4,A:A,1))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Hi Jason, I appreciate this input, thanks. But I noticed this formula does not use the criteria on sheet 1. Perhaps I should give more details;

1. The formula should normally use only the unique week/name criteria details on sheet 1 to add the unique MAX order values on sheet 2.
2. The week/name details on sheet 1 are variable (you can even have only one name and one week, depending on user input). On sheet 2, for every week populated, we would always have all names and corresponding values.
3. I noticed there might be some error on sheet 2, because only one value for one week is possible for each name. SO please assume the 7000 is for John week 2. This is my mistake, sorry.

 

[jasonb75]

I'm lost. Please proved a clearer example of each sheet, with the expected results typed in so that we have something to aim for.

Please post your samples using XL2BB (link below) instead of screen captures.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Duke92]

I have made the data to reflect the reality I'm considering. I also downloaded the addin now, and tried to copy my range into this.

1. As in Sheet 1. We now have mixed week/name combinations
2. The answer currently shows 16300 which is adding all unique occurences for name/week combinations in week 2
3. What I'm looking for is only to add the MAX values for all week/name in sheet 2, using only the unique week/name combinations in sheet 1. So the right formula should only pick 7000 from wk 2 for John + 3000 from wk 2 for Paul + 200 from week 1 for Peter = 10200.

I am happy to clarify if anything is not clear

Sheet 1

SUMIF Trouble (1) (1).xlsx
	A	B	C	D	E	F	G	H
1
2						Unique Sum for all Unique Name/week combinations
3	Week	Name	Orders			16300
4	1	John
5	1	Peter
6	1	Paul
7	2	John
8	2	Peter
9	1	Paul
10
11
12
13
14
15
16
Sheet1

Cell Formulas
Range	Formula
F3	F3	=SUMPRODUCT(INDEX(--(COUNTIF(A3:A9,Sheet2!A:A) * COUNTIF(B3:B9,Sheet2!B:B)>0),0),Sheet2!C:C)

Sheet 2

SUMIF Trouble (1) (1).xlsx
	A	B	C	D
1	Week	Name	Orders
2	1	John	5000
3	1	Peter	200
4	1	Paul	1000
5	2	Peter	100
6	2	Paul	3000
7	2	John	7000
8
9
10
Sheet2

I

 

[jasonb75]

You don't need to quote, I still get the notifications.

Is 16300 the correct result for the example? If not, then what is the correct result.

If it is correct, then please provide an example where it is not correct and tell us what the correct result should be.

 

[Duke92]

Thanks for your inputs.
The current example is a scenario where it is not correct. The correct result as I stated is 10,200

 

[jasonb75]

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.