How do I fix this Sumproduct formula?

Duke92

New Member
Joined
May 26, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • 1.PNG
    1.PNG
    11.6 KB · Views: 9
  • 2.PNG
    2.PNG
    9.5 KB · Views: 9
Last edited by a moderator:

Duke92

New Member
Joined
May 26, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
This appears impossible with a single formula, this method looks like it works.
Book2
ABCFGHK
1WeekNameOrders
21John5000Unique Sum for all Unique Name/week combinations
31Peter 200WeekNameOrders10200
41Paul10001John5000
52Peter 1001Peter 200
62Paul30001Paul1000
72John70002John7000
83Paul15002Peter 100
92Paul3000
Sheet3
Cell Formulas
RangeFormula
K3K3=SUM(IFERROR(MAXIFS(H:H,G:G,$G$4:$G$9)/COUNTIF(G:G,$G$4:$G$9),0))
H4:H9H4=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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Duke92

New Member
Joined
May 26, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,459
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 26, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLM
1WrongCorrect
22220015200
3WeekNameWeekNameOrders
41John1John5000
52Peter 1Peter 200
62Paul1Paul1000
71John1John7000
82John9000
92Peter 200
102Paul8000
112John15000
12
13
Sheet1
Cell Formulas
RangeFormula
K2K2=SUM(MAXIFS(H:H,G:G,Sheet1!$B$4:$B$9,F:F,Sheet1!$A$4:$A$9))
L2L2=H7+H9+H10
Press CTRL+SHIFT+ENTER to enter array formulas.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,459
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 26, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,279
Messages
5,571,284
Members
412,375
Latest member
BRJoeyMelo
Top