Sumproduct and Max

xl_psychic

Active Member
Joined
Jan 4, 2004
Messages
359
All:

I am making use of the following formula in order to get the maximum value within a range.

=SUMPRODUCT(MAX(($B$9:$B$1110=$B$7)*(L$9:L$1110<>"")*(L$9:L$1110)))

But the formula is giving me a #VALUE output.

Could you please let me know where am I going wrong with this.

Help Appreciated...
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi,

Propably because you have "formula blanks"

Try:

=MAX(IF($B$9:$B$1110=$B$7,L$9:L$1110))

Confirmed with Ctrl + shift + enter.
 
Upvote 0
Fairwinds:

Could you explain me the "formula blanks" concept.. I didn't quite get it..

I ended up using the MAX(IF... Array formula..
 
Upvote 0
xl_psychic said:
Fairwinds:

Could you explain me the "formula blanks" concept.. I didn't quite get it..

I ended up using the MAX(IF... Array formula..


I mean cell holding e.g.

=""
 
Upvote 0
Fairwinds:

The formula wouldn't work even if the "(L$9:L$1110<>"")" part was removed from the same..

I added this part after the initial formula =SUMPRODUCT(MAX(($B$9:$B$1110=$B$7)*(L$9:L$1110))) too gave me a #VALUE error..

Any reasons for the same..
 
Upvote 0
xl_psychic said:
Fairwinds:

The formula wouldn't work even if the "(L$9:L$1110<>"")" part was removed from the same..

I added this part after the initial formula =SUMPRODUCT(MAX(($B$9:$B$1110=$B$7)*(L$9:L$1110))) too gave me a #VALUE error..

Any reasons for the same..

=SUMPRODUCT(MAX(--($B$9:$B$1110=$B$7),L$9:L$1110))

would succeed but would be undesirable call to SumProduct. Some people do so at MS worksheet.functions. Looks like they want to generalize their beloved function and/or to avoid confirming a formula with control+shift+enter. MAX can also grind computed arrays if Excel is properly instructed with the control+shift+enter sequence (see Fairwinds's suggestion).
 
Upvote 0
fairwinds said:
Aladin Akyurek said:
=SUMPRODUCT(MAX(--($B$9:$B$1110=$B$7),L$9:L$1110))


Have the students been tough today?
:LOL:

The 2nd years are busy writing an extended report covering a collection of models they had to set up last two weeks... I guess they don't have time for that. :LOL:
 
Upvote 0
Great.

That should leave the teacher plenty of time to put together a SUMPRODUCT formula that actually takes the condition into consideration. :LOL:
 
Upvote 0

Forum statistics

Threads
1,214,324
Messages
6,118,892
Members
448,854
Latest member
Eduard_Stoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top