# How do I fix this Sumproduct formula?

#### Duke92

##### New Member
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
11.6 KB · Views: 9
• 2.PNG
9.5 KB · Views: 9
Last edited by a moderator:

#### Duke92

##### New Member
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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Duke92

##### New Member
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
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
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
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
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!

Replies
8
Views
564
Replies
10
Views
289
Replies
2
Views
56
Replies
2
Views
115
Replies
10
Views
372

1,119,073
Messages
5,575,980
Members
412,693
Latest member
SJC2020