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:

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

jasonb75

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

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

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

Duke92

New Member
Joined
May 26, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

jasonb75

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

 

Duke92

New Member
Joined
May 26, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

jasonb75

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,118,073
Messages
5,570,032
Members
412,306
Latest member
bobbyabradley
Top