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: 12
  • 2.PNG
    2.PNG
    9.5 KB · Views: 14
Last edited by a moderator:
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
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top