• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summing Highest Values

fixthis

New Member
Hi,

Looking for shorter formula to sum the highest values for each part description type.

This formula does work:

Code:
=SUM(MAX(IF(--ISNUMBER(SEARCH("CM2000",$C$3:$C$14)),$F$3:$F$14)),MAX(IF(--ISNUMBER(SEARCH("CM4000",$C$3:$C$14)),$F$3:$F$14)),MAX(IF(--ISNUMBER(SEARCH("CM5077",$C$3:$C$14)),$F$3:$F$14)))


Running Excel 2010 on Windows 7.
 

Attachments

  • Part Factor Summing of Max Values.xlsx
    14 KB · Views: 23
Thank you Nebu for your pivot solution. However, I am looking for a formula approach as the actual spreadsheet is setup a bit different and I need the sum in the one cell and no helper columns.
 
Hi:

I don't think there is any other straight forward way of doing this without using helper column.

Thanks
 
Fortunate timing, since you can adapt Lori's excellent set-up (given recently here: http://chandoo.org/forum/threads/find-the-minimum-every-line-and-summarize.31427/#post-187323), viz, array formula**:

=SUM(LARGE($F$3:$F$14,MOD(LARGE({1,2,3}*10^6+RANK(IF(ISNUMBER(SEARCH({"CM2000","CM4000","CM5077"},$C$3:$C$14)),$F$3:$F$14),$F$3:$F$14),{1,2,3}*ROWS($F$3:$F$14)),10^6)))


I (rather lazily) used the static {1,2,3} (representing the fact that there are 3 search criteria, i.e. "CM2000","CM4000" and "CM5077") here. This can of course be replaced with a dynamic equivalent should the number of search criteria be a variable.

Regards
 
Last edited:
Just for fun.

Using CUBE functions.

First add data range to data model via PowerQuery/MS Query.
Create PivotTable and add appropriate measures, then convert to OLAP and delete. This will leave measures in Data Model but no physical PivotTable.

Enter in B18:
=CUBESET("ThisWorkbookDataModel","[Table1].[Part_List].children","Part_List",2,"[Table1].[Factor]")

B19 & Drag down:
=CUBERANKEDMEMBER("ThisWorkbookDataModel",$B$18,ROW(A1))

F18:
=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Max of Factor]","Factor")

F19 & Drag Down:
=CUBEVALUE("ThisWorkbookDataModel",$F$18,B19)

See attached sample.

To do it in single cell. No need for PivotTable in this case.
See transformation done in "Agg1" query and the formula in F23.

Edit: Forgot to mention that "Part Description" column was split using M function. You can do it via GUI as well.
 

Attachments

  • Part Factor Summing of Max Values (1).xlsx
    185.8 KB · Views: 10
Last edited:
Thank you XOR LX as your solution does work albeit a bit more complicated than I imagined. Not sure if it is more efficient than my original formula solution calling SEARCH/MAX three times.

Chihiro, very impressive solution but I apologize as your solution is way too complex for me. I have been using Excel for decades and never new about the CUBESET functions. I could not even find the named range "ThisWorkBookDataModel" or what ever it is called.

I guess I was still hoping for something in the neighborhood of this:

Code:
=SUMPRODUCT(--ISNUMBER(SEARCH({"CM2000","CM4000","CM5077"},$C$3:$C$14))+LARGE($F$3:$F$14,1))
 
Hi ,

Two points :

1. The parts list you have entered in the range B19:B21 is different from what you have entered in your formula ; this needs to be corrected.

It also illustrates the mistake in using constant values within a formula , since it is easier to find an inconsistency when data is in a worksheet cell , than when it is part of a formula.

2. If you extend your formula in its present version , imagine what it will look like when you have say a dozen items in your parts list !

From both considerations , it is better if a helper column is used , in the range C19:C21 , having the array-entered formula :

=MAX(IF(ISNUMBER(SEARCH(B19, $C$3:$C$14)), $F$3:$F$14))

entered in C19 and copied down.

The output formula merely sums these 3 figures.

Not only is this easy to understand for even a beginner , it is easily scalable to any number of parts in the parts list.

Narayan
 
Thank you XOR LX as your solution does work albeit a bit more complicated than I imagined. Not sure if it is more efficient than my original formula solution calling SEARCH/MAX three times.

Perhaps not, though the solution I gave is readily extendable to work for any number of search criteria, whereas that you give would require an additional clause for each.

Regards
 
Hi,

Try this formula,

=SUM(MAX(COUNTIF(B19,MID(C3:C14,1,6))*F3:F14),MAX(COUNTIF(B20,MID(C3:C14,1,6))*F3:F14),MAX(COUNTIF(B21,MID(C3:C14,1,6))*F3:F14))

array formula.

David
 
Hi to all!

Just for fun, with no code into PowerQuery is done.

Check file. Blessings!
 

Attachments

  • Part Factor Summing of Max Values.xlsx
    21.9 KB · Views: 5
Back
Top