# Sumproduct and Max

#### xl_psychic

##### Active Member
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.

Fairwinds:

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

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

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.

=""

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..

Yes.

...)*(L\$9:L\$1110)))

If you try to multiplicate a formula blank you get an error.

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).

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

Have the students been tough today?

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

Have the students been tough today?

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.

Great.

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

Replies
2
Views
106
Replies
9
Views
256
Replies
2
Views
83
Replies
1
Views
121
Replies
3
Views
127

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.

### Which adblocker are you using?

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

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