Can Sumproduct and MAX/MIN be combined?

Can Sumproduct and MAX/MIN be combined?

I need to calculate the MAX and MIN for values with a particular criteria stored in Multiple sheets as well as different rows..


I have 5 sheets and the Sheets are going to keep on Increasing,
I want to know the Highest from the Different Sheets, but this information is for a particular individual whose Records appear in Different Rows in all the Sheets...

If it was in the same Row , then it would have been compartively easier by simply using this formula..

[ =MAX(Sheet1:Sheet3!B3:B4) ]
[ =LARGE(Sheet1:Sheet3!B3:B4,1) ]

But I want to know the Max of one Individual whose data is not only spread across different Sheets but also in Different Rows...

Can SUMPRODUCT & MAX/MIN be combined to get the above?

Please help me on this?

You can use multiple ranges with either function:

=max(rng1, rng2, rng3, ...)
=large( (rng1, rng2, rng3, ...), 1)

Note the extra set of parens in the second example.

Originally Posted by shg

You can use multiple ranges with either function:

=max(rng1, rng2, rng3, ...)
=large( (rng1, rng2, rng3, ...), 1)

Note the extra set of parens in the second example.

I want a formula which can work in such a way that whenver we add or delete tabs as long as we update the Name of the Tab in the column A as shown, it should pick the MAX and MIN from all the other sheets…

Now the data is not only In Multiple Sheets but also in Different Rows and the Runs are also suffixed with an asterik (*) when the Batsman is Not Out..

For Ex:- Andrew Silmoure has scored
L& I - 14* where he is Not Out
J P Morgan - 5
H P - 6
Dell - 100
Hell - 100* - Not out

Highest score would be 100* and lowest would be 5....

BUmP.............

Hi all4excel



Look at the attachment below. I had to add some helper columns in your workbook to get these formula to work. I also add some helper columns F & G just unhide them and you'll see the formulas. Also I used a formula so you don't have to list your worksheets. But you would have to check mark your Iteration in excel go to tools options and calculation. If not check mark it will give you a ref# error.


P.S


If you would like to know how the formulas work go to

Tools > Formula Auding > Evaluate formula


Hope this helps!

Originally Posted by vane0326

Because I was ignoreing the wrong worksheet.


I changed this formula


TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(wks))))<>MATCH("L&Twks,0)


to this:


TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(wks))))<>MATCH("Consolidation",wks,0)

Also can you add the additional columns with the formulas you're using then I would see if could it to work. If you would like to know more about these types of formulas try doing a search on other Excel fourms or just experiment with them thats how I learned.

Vane,

Have added the additional columns with the Number and the Asterisk seperately

I have also understood about the Sheet you wanted to ignore..

TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(wks))))<>MATCH( "Consolidation",wks,0)

Got It..!

I think we can use this to validate this Sheet every time even when the Name is changed.

[ RIGHT(CELL("filename",$C$2),LEN(CELL("filename",$C$2))-FIND("]",CELL("filename",$C$2),1))]

Maybe a safer alternative would be

[ MATCH( RIGHT(CELL("filename",$C$2),LEN(CELL("filename",$C$2))-FIND("]",CELL("filename",$C$2),1)),wks,0) ]

There are still some anomalies...Please go through that and moreover the Columns F & G in the tab "Consolidation" have gone for a Toss.. because of the changes..

This is excellent work..!

I was trying to get a solution for so many weeks but ultimately got it..
I am really very happy as my Cricket Project is nearing completion..

There are no more Anomalies everthing's working the way its intended to..

Just one question though...
I got the Highest and Lowest Scores...from you ..

Can I use the same Analogy to get the Best Bowling Figures?

Best bowling Figures--This would be based on the Number of wickets and the number of runs and should be displayed as 3-36 which means that the bowler took 3 wickets at the cost of 36 runs in a match..

But if there are more than one occasion of taking the same number of wickets then the runs conceded should be given weightage..

Example :
1. 3-39
2. 3-26

Naturally 3-26 should be the Best bowling figures.

Again if the runs are same then the number of wickets would be considered in the Best bowling figures.

1. 3-36
2. 1-36

Naturally 3-36 should be the Best bowling figures..

Now, Im sorry that Im asking you another question in the same thread the reason being you have already solved my first query and this is based on the same Logic of MAX and MIN..

As explained above the Wickets taken by a Bowler are going to be crucial and are located in the Column K and the Runs given in Column N after all the Helper columns which are hidden in the five sheets.

I had got this code from Daddylonglegs in this thread but this works in a Single Sheet...

[ =MAX(Wickets)&"-"&MIN(IF(Wickets=MAX(Wickets),Runs Given)) ]

With your help I can get Max wickets and Min Runs and then just append the two.

http://www.excelforum.com/showthread.php?t=638316

I'm attaching a File for reference if you could also help me on the same..

Though you have already helped me on query..

I used the Max & Min Test4 file instead because it already has the helper columns in those worksheets.

Dear vane,

Your formula works excellently, no doubts about it however when I tried to add it to my Sheet with other details its not getting added.

I mean the Name---> Criteria3

[ Criteria3-MAX(IF(TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(Tabnames))))<>MATCH("Consolidation",Tabnames,0),IF(T(OFFSET(INDIRECT("'"&Tabnames&"'!A3:A13"),ROW(INDIRECT("3:13"))-3,0,1))=$A2,N(OFFSET(INDIRECT("'"&Tabnames&"'!H3:H13"),ROW(INDIRECT("3:13"))-3,0,1))))) ]

I am able to add the formula but somehow not able to add the criteria it keeps on giving error..

Im using Excel-2003...

So please advise on the same...

Originally Posted by vane0326

I used the Max & Min Test4 file instead because it already has the helper columns in those worksheets.

Please revert ....

I have a New Query but I would be starting a New Thread, would appreciate if you help as you have done the major stuff..

Its connected with the same file...

Thanks a lot for all your help...

Originally Posted by all4excel

Dear vane,

Your formula works excellently, no doubts about it however when I tried to add it to my Sheet with other details its not getting added.

I mean the Name---> Criteria3

[ Criteria3-MAX(IF(TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(Tabnames))))<>MATCH("Consolidation",Tabnames,0),IF(T(OFFSET(INDIRECT("'"&Tabnames&"'!A3:A13"),ROW(INDIRECT("3:13"))-3,0,1))=$A2,N(OFFSET(INDIRECT("'"&Tabnames&"'!H3:H13"),ROW(INDIRECT("3:13"))-3,0,1))))) ]

I am able to add the formula but somehow not able to add the criteria it keeps on giving error..

Im using Excel-2003...

So please advise on the same...

You would have to check mark your Iteration in excel go to tools options and calculation. If not check mark it will give you a ref# error.


Now if you don't want to use this option then we will need to list your worksheets.

Hi all4excel



If you have Excel 2007 we would not have to check mark the Iteration but Excel 2003 we would have to. But here is an attachment below that using a reference list of worksheets.

Originally Posted by vane0326

Hi all4excel



If you have Excel 2007 we would not have to check mark the Iteration but Excel 2003 we would have to. But here is an attachment below that using a reference list of worksheets.

I am really greatful to you for all your help uptill now can u also help me in the other thread where I need the name of the tabs or sheets containing the Highest and Lowest Scores and best bowling figures..

You have already worked on this Important portion...