Jun 17 2020 02:55 AM
Hi,
I am trying to create a basic SUMPRODUCT formula in Excel for Mac v16.36:
=SUMPRODUCT(--(B16>{$A3,$A4,$A5,$A6,$A7}),--(B16-{$A3,$A4,$A5,$A6,$A7}),{B$3,B$4-B$3,B$5-B$4,B$6-B$5,B$7-B$6})
I keep getting the "There's a problem with this formula" pop-up. The formula is correct and works perfectly fine in Google Sheets.
What am I doing wrong? Thank you.
Jun 17 2020 04:43 AM
Excel does not allow range references inside { } - you can only put constants inside the {} syntax.
You need to convert your formula to an Excel-style array formula. If you have Dynamic Array Excel it will automagically be enetered as an array formula, otherwise you need to use Control-Shift-Enter to enter the bformula
Jun 17 2020 04:53 AM
Thank you. Would you kindly walk me through the steps? I would be very grateful.
Jun 17 2020 06:59 AM
Jun 17 2020 07:26 AM
You can check for dynamic array version by seeing if you have the XLOOKUP function.
If B2 contains text or zero try this
=SUMPRODUCT(--(B16<$A3:$A7),--(B16-$A3:$A7),(B$3:B$7-N(B$2:B$6)) )
Jun 17 2020 07:55 AM
Thank you. Is there supposed to be a N before the B2-B6? I am getting a strange result.
Jun 17 2020 08:02 AM
Jun 17 2020 10:06 AM
yes you need the N if B2 contains anything other than zero
What is in B2?
What result do you expect?
What result do you get?
Jun 17 2020 10:19 AM
@fastexcel I've attached a screen shot below. The row in yellow is the set of correct values. Thank you once again for your help.
Jun 17 2020 10:40 AM
the last part of the formula you have entered is not the same as the formula I used.
You are using ,--(B$4:B$8)-N(B$3:B$7))
Try ,(B$4:B$8-N(B$3:B$7)))
Jun 17 2020 11:16 AM
SolutionThanks for the screenshot: I had a mistake in the formula and also forgot that N() does not return an array. And you don't need the N if B3 is zero.
I think this formula gives you the correct result.
You do not need to array-enter it since SUMPRODUCT always calculates in array fashion. Also you don't actually need the -- in --(B14-$A4:$A8) since there are no True/False to convert to 1s and zeros.
=SUMPRODUCT(--(B14>$A4:$A8),--(B14-$A4:$A8),(B$4:B$8-B$3:B$7) )
Jun 17 2020 01:18 PM
@fastexcel Worked like a charm. Two observations:
1. I don't need the 0s in the top row. It works just as well when that row is blank.
2. It doesn't work without the --
Screen shots attached. Thank you very much.
Jun 17 2020 02:57 PM
you need -- on the first sumproduct parameter but not on any of the others.
Yes blank is treated as zero ...
Jun 18 2020 01:20 AM
Just in case, if row 3 is occupied as
it could be
=(B14-LOOKUP(B14,$A$4:$A$8))*LOOKUP(B14,$A$4:$A$8,B4:B8)+SUMPRODUCT(($A$5:$A$8<B14)*($A$5:$A$8-$A$4:$A$7)*B4:B7)
Jun 18 2020 01:18 PM
Jun 19 2020 12:42 AM
The first parameter has a > operator which means that the result of the expression will be an array of True or False. SUMPRODUCT will error with True/False so the True/False needs to be converted to numbers, which is done using the --. The other parameters do not have a comparison operator so do not need this.
Jun 17 2020 11:16 AM
SolutionThanks for the screenshot: I had a mistake in the formula and also forgot that N() does not return an array. And you don't need the N if B3 is zero.
I think this formula gives you the correct result.
You do not need to array-enter it since SUMPRODUCT always calculates in array fashion. Also you don't actually need the -- in --(B14-$A4:$A8) since there are no True/False to convert to 1s and zeros.
=SUMPRODUCT(--(B14>$A4:$A8),--(B14-$A4:$A8),(B$4:B$8-B$3:B$7) )