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