Error message when using {} to evaluate multiple criteria

[Jeffery Paul]

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,

 

[tygrrboi]

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

 

[MarcelBeug]

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:

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

 

[Jeffery Paul]

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:

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