Error message when using {} to evaluate multiple criteria

Jeffery Paul

New Member
Joined
Jul 13, 2016
Messages
2
I have a large data set and need to evaluate based on multiple criteria in multiple columns. Here is a sample:

Name AB R H Date
Altuve, J 4 2 1 5-Apr-16 Start Date 5-Apr
Altuve, J 3 1 1 6-Apr-16 End Date 10-Apr
Altuve, J 4 0 0 7-Apr-16
Altuve, J 5 0 1 8-Apr-16 Altuve, J
Altuve, J 5 1 1 9-Apr-16 Andrus, E
Altuve, J 3 1 1 10-Apr-16 Arenado, N
Andrus, E 2 1 0 4-Apr-16
Andrus, E 4 0 2 5-Apr-16
Andrus, E 1 0 0 7-Apr-16
Andrus, E 5 0 2 8-Apr-16
Andrus, E 4 1 1 9-Apr-16
Andrus, E 4 0 0 10-Apr-16
Arenado, N 5 1 2 4-Apr-16
Arenado, N 4 0 0 5-Apr-16
Arenado, N 4 0 0 6-Apr-16
Arenado, N 3 0 1 8-Apr-16
Arenado, N 3 1 0 9-Apr-16
Arenado, N 4 1 2 10-Apr-16

When I use the formula =SUMPRODUCT(($E$2:$E$19>=$H$2)*($E$2:$E$19<=$H$3)*(($A$2:$A19=$G$5)+($A$2:$A$19=$G$6)+($A$2:$A$19=$G$7))*($B$2:$B$19)), I get the proper response of 60 at bats.

However, when I try to use braces {} like I've seen in many online examples to compact the formula =SUMPRODUCT(($e$2:$e$19>=h$2)*($e$2:$e$19<=h$3)*($a$2:$a$19={g5:g7})*($b$2:$b$19)), I get the following error message

"There's a problem with this formula

Not trying to type a formula?
When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula:"


Any help would be greatly appreciated as I will need to do many versions of this formula for all of my evaluations.

Thanks in advance,
 
If you want to input an array formula into excel, you do not type the {} brackets manually.

Just type it without them and finish with CONTROL + SHIFT + ENTER instead of just enter
 
Upvote 0
I think this is a different case: if you want to use curly brackets for an array of values (so not for an array formula), you can only use values between the curley brackets, not cell references.

My suggestion would be the following array formula, to be confirmed with Ctrl+Shift+Enter:
Code:
=SUMPRODUCT(($E$2:$E$19>=$H$2)*($E$2:$E$19<=$H$3)*($A$2:$A19=TRANSPOSE($G$5:$G$7))*($B$2:$B$19))
 
Upvote 0
I think this is a different case: if you want to use curly brackets for an array of values (so not for an array formula), you can only use values between the curley brackets, not cell references.

My suggestion would be the following array formula, to be confirmed with Ctrl+Shift+Enter:
Code:
=SUMPRODUCT(($E$2:$E$19>=$H$2)*($E$2:$E$19<=$H$3)*($A$2:$A19=TRANSPOSE($G$5:$G$7))*($B$2:$B$19))

Thank you Marcel. The above worked great for my example.

Ultimately, I will need to evaluate based on multiple criteria in multiple columns. I tried to add a 2nd array to the formula ($f$2:$f$19=transpose($I$5:$I$7)) but it only returned the value for "I5".
 
Upvote 0

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