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:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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).
 
Upvote 0
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
 
Upvote 0
See if this helps, the '1' at the end of the formula denotes the week number.
Book2
ABCDEF
21John5000Peter
31Peter200John
41Paul1000Paul
51John70008200
Sheet3
Cell Formulas
RangeFormula
F5F5=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.
 
Upvote 0
See if this helps, the '1' at the end of the formula denotes the week number.
Book2
ABCDEF
21John5000Peter
31Peter200John
41Paul1000Paul
51John70008200
Sheet3
Cell Formulas
RangeFormula
F5F5=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.
 
Upvote 0
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.

 
Upvote 0
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
ABCDEFGH
1
2Unique Sum for all Unique Name/week combinations
3WeekNameOrders16300
41John
51Peter
61Paul
72John
82Peter
91Paul
10
11
12
13
14
15
16
Sheet1
Cell Formulas
RangeFormula
F3F3=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
ABCD
1WeekNameOrders
21John5000
31Peter 200
41Paul1000
52Peter 100
62Paul3000
72John7000
8
9
10
Sheet2



I
 
Upvote 0
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.
 
Upvote 0
Thanks for your inputs.
The current example is a scenario where it is not correct. The correct result as I stated is 10,200
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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