SUMIF trouble with Unique Values & Summing the MAX Values

[visk92]

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 in Column F, , for all occurences of John week 1 in sheet 1, I need to include the sum of John Week 1 from sheet 2 ONLY ONCE, in an aggregate sum of all other unique name/week combinations as in sheet 1.

In Column F highlighted green, I need ONE single unique total value of all items from Sheet 1. I suppose this uses some combination of COUNTIF and SUMPRODUCT/SUMIF but i have not figured this out.

For the example in the sheet for all names, the answer im looking for should be 6200. Not 12400


i'LL APPRECIATE SOME HELP
Attached Files Attached Files

[Sunny18pc]

Hi check this out;

Please Login or Register  to view this content.

[visk92]

Hi Thanks for the post. But this doesn't help much as it only counts the number of occurrences for each item

[hrlngrv]

I may be missing something, but how about

=SUMPRODUCT(INDEX(--(COUNTIF(A4:A9,Sheet2!$A$2:$A$4)*COUNTIF(B4:B9,Sheet2!$B2:$B4)>0),0),Sheet2!$C$2:$C$4)

in some cell in Sheet1.

[CARACALLA]

	A	B	C	D	E	F
2						Unique Sum for all Unique Name/week combinations
3	Week	Name	Orders			6200
4	1	John	5000
5	1	Peter	200
6	1	Paul	1000
7	1	John	5000
8	1	Peter	200
9	1	Paul	1000

F3=SUM(IF( FREQUENCY(IF(C4:C9<>"",IF(A4:A9=A4,C4:C9)),C4:C9),C4:C9))

control+shift+enter

[visk92]

I may be missing something, but how about

=SUMPRODUCT(INDEX(--(COUNTIF(A4:A9,Sheet2!$A$2:$A$4)*COUNTIF(B4:B9,Sheet2!$B2:$B4)>0),0),Sheet2!$C$2:$C$4)

in some cell in Sheet1.

Wow, this perfectly works! Thank you!

[visk92]

	A	B	C	D	E	F
2						Unique Sum for all Unique Name/week combinations
3	Week	Name	Orders			6200
4	1	John	5000
5	1	Peter	200
6	1	Paul	1000
7	1	John	5000
8	1	Peter	200
9	1	Paul	1000

F3=SUM(IF( FREQUENCY(IF(C4:C9<>"",IF(A4:A9=A4,C4:C9)),C4:C9),C4:C9))

control+shift+enter

I believe this would have worked but it does not link criteria from sheet 1 to sheet 2

[visk92]

Hi all,

So coming back to this, I need some help on this same topic. I am now looking to return only the sum of maximum unique order values for all the name/week combinations from sheet 1.

So essentially F3 in sheet one should add from sheet 2 = 7000 for John + 1000 + 200. 7000 being the max for john.

I will appreciate any help

[visk92]

Wow, this perfectly works! Thank you!

Please note that my sheet is currently built on this formula. I just need to modify it to pick only max values

[visk92]

I may be missing something, but how about

=SUMPRODUCT(INDEX(--(COUNTIF(A4:A9,Sheet2!$A$2:$A$4)*COUNTIF(B4:B9,Sheet2!$B2:$B4)>0),0),Sheet2!$C$2:$C$4)

in some cell in Sheet1.

Please note that my sheet is currently built on this formula. I just need to modify it to pick only max values

[visk92]

Any ideas guys?

[AliGW]

You have marked the thread as solved ...

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

[visk92]

Just marked as unsolved

[visk92]

When I use this formula =SUMPRODUCT(INDEX(--((MAX(COUNTIF(A3:A9,Sheet2!A1:A5))) * (MAX(COUNTIF(B3:B9,Sheet2!B1:B5)))>0),0),MAX(Sheet2!C1:C5)), I get 7000. But the answer should be a total of all max values which is 8200.

Will appreciate any inputs

[visk92]

Can't believe this is that hard I can clarify if something isn't clear

[AliGW]

So you expect somebody to have sorted it out for you overnight, do you? That sort of comment will not endear you to people who are giving freely of their time to help here. Be patient, please.

[visk92]

Oh true, but didn't intend that way.

[AliGW]

The original formula does not really work. If you extend the range to cover all rows in Sheet 2 like this:

=SUMPRODUCT(INDEX(--(COUNTIF(A4:A9,Sheet2!$A$2:$A$5)*COUNTIF(B4:B9,Sheet2!$B2:$B5)>0),0),Sheet2!$C$2:$C$5)

you will get this: 13200

I think you need to start again (I don't mean open a new thread!) - in the original query, you seem to want to count the first match for each unique combination on sheet 2 and sum these values - is this correct?

[visk92]

Actually this was correct for the initial purpose, and at the time of first posting 13200 will be the answer. But with the new development, I am only looking for the maximum of each unique combination on sheet 2 (so to skip 5k for john and instead add john's max 7k john + 1000 Paul + 200 Peter instead. To do that for every instance where a maximum value exists for each name entry).

Perhaps I was thinking this could be tweaked using the MAX function somehow. But perhaps you are right, and the entire formula may need to change to get this result

[AliGW]

OK - thanks for clarifying.

When you attach a sample workbook, it needs to (a) contain enough sample data to test solutions properly and (b) contain manually worked out expected results, not a cell with question marks in it.

[visk92]

Alright, noted. Thanks

[JeteMc]

What about using a pivot table?
The Orders are placed in the values field which is summarized by Max
A Calculated Item is added: =John+Paul +'Peter '
Let us know if you have any questions.

[visk92]

What about using a pivot table?
The Orders are placed in the values field which is summarized by Max
A Calculated Item is added: =John+Paul +'Peter '
Let us know if you have any questions.

Thanks for your reply. Under normal circumstances this will work. But the context I'm currently in needs a formula because it is inserted to a cell via a macro. Some other things to note.
- The data in sheet 1 is already pasted in by a macro from a pivot table

- The formula is inserted by the same macro in the last phase of the code, in an automated process which picks the week/name combination from sheet 1, match with the max order values for each week/name combination in sheet two and just return the sum of all of these in cell F3.

With another pivot, it'll be a bit more manual and outside the current design. Nonetheless, thanks for the input

[XOR LX]

Hi,

So week numbers other than 1 are possible? If so, I think it would make sense for you to post a new workbook with a more realistic example together with expected result.

Regards

[visk92]

Hi,

So week numbers other than 1 are possible? If so, I think it would make sense for you to post a new workbook with a more realistic example together with expected result.

Regards

Hi, indeed I might have been a bit unclear with the sample.


A more representative sample is attached where the problem has been solved with the formula =SUM(IFERROR(MAXIFS(Sheet2!C:C,Sheet2!B:B,Sheet1!$B$4:$B$9,Sheet2!A:A,Sheet1!$A$4:$A$9)/COUNTIFS(A:A,Sheet1!$A$4:$A$9,B:B,Sheet1!$B$4:$B$9),0))

As you will see in attachments, there can be more than one entry per name/week for each item on sheet 1, and multiple orders for each name/week on sheet 2.
The formula just picks the max order value of only one of such possible name/week combos from sheet 1 and adds up every such instance for all possible name/week combos available.

Apparently, it was more of a maxifs/countifs problem and I got some insights to this when I prepared a clearer more representative example. I thought to share here for anyone looking for help with same problem.

Thanks to everyone who helped out for the time and efforts.

[JeteMc]

Shouldn't the sum of the max's per week/name be 31,400?