Excel 2016 (Windows) 32 bit | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Jim | Without CSE (Bad)-----> | 1 | ||
2 | Adam | With CSE (Good)-----> | 3 | ||
3 | Ben | Without CSE (Good)-----> | 3 | ||
4 | Adam | ||||
5 | Jim | ||||
6 | Adam | ||||
7 | Ben | ||||
8 | Adam | ||||
9 | Jim | ||||
10 | Adam | ||||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C1 | =SUM(--(A1:A10="Jim")) | |
C3 | =SUMPRODUCT(--(A1:A10="Jim")) | |
C2 | {=SUM(--(A1:A10="Jim"))} | |
Press CTRL+SHIFT+ENTER to enter array formulas. |
How do i know I have an array formula? For example, how do I know when to add brackets around this array formula?
{B2:I2*B3:BI}
And also with this?
{=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))}
If you have bits with
[1] pairwise multiplications like
(A2:A4)*(B2:B4)
(A2:A4="jim")*(B2:B4)
[2] with IF(s) which act(s) as filter like
IF(A2:A4>=2,1)
[3] with multi-valued references which serve as look up value or comparison value like...
MATCH(A2:A4,B2:B4,0)
A2:A4=B2:B4
you have to do with an array-processing formula.
The foregoing list is probably not exhaustive. The gist is: bits leading to multi-value results indicate array-processing, therefore control+shift+enter if the receiving function is not designed exlusively for array-objects like SUMPRODUCT.
Hope this helps.
Very confused by your answer. Would you further explain??
Excel 2010 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | HXN | GEH | TZD | 11 | |||||
2 | jim | ben | alice | 30 | 30 | ||||
3 | WOA | ben | BAK | 23 | 45 | ||||
4 | FXN | BUW | EIG | 71 | |||||
5 | jim | JXP | alice | 4 | |||||
6 | KPU | ben | RTH | 83 | |||||
7 | JCH | LO | LZB | 50 | |||||
8 | jim | ben | alice | 15 | |||||
9 | jim | UBJ | ICO | 85 | |||||
10 | jim | JCR | HIF | 54 | |||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2 | =SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10)) | |
G3 | {=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))} | |
Press CTRL+SHIFT+ENTER to enter array formulas. |
If you have bits with
[1] pairwise multiplications like
(A2:A4)*(B2:B4)
(A2:A4="jim")*(B2:B4)
[2] with IF(s) which act(s) as filter like
IF(A2:A4>=2,1)
[3] with multi-valued references which serve as look up value or comparison value like...
MATCH(A2:A4,B2:B4,0)
A2:A4=B2:B4
you have to do with an array-processing formula.
The foregoing list is probably not exhaustive. The gist is: bits leading to multi-value results indicate array-processing, therefore control+shift+enter if the receiving function is not designed exlusively for array-objects like SUMPRODUCT.
Hope this helps.
If you have bits with
[1] pairwise multiplications like
(A2:A4)*(B2:B4)
(A2:A4="jim")*(B2:B4)
[2] with IF(s) which act(s) as filter like
IF(A2:A4>=2,1)
[3] with multi-valued references which serve as look up value or comparison value like...
MATCH(A2:A4,B2:B4,0)
A2:A4=B2:B4
you have to do with an array-processing formula.
The foregoing list is probably not exhaustive. The gist is: bits leading to multi-value results indicate array-processing, therefore control+shift+enter if the receiving function is not designed exlusively for array-objects like SUMPRODUCT.
Hope this helps.
Aladin and Dave explained it more thoroughly, but here's another example:
Excel 2010
A B C D E F G 1 HXN GEH TZD 11 2 jim ben alice 30 30 3 WOA ben BAK 23 45 4 FXN BUW EIG 71 5 jim JXP alice 4 6 KPU ben RTH 83 7 JCH LO LZB 50 8 jim ben alice 15 9 jim UBJ ICO 85 10 jim JCR HIF 54
<tbody>
</tbody>Sheet2
Worksheet Formulas
Cell Formula G2 =SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))
<tbody>
</tbody>
<tbody>
</tbody>
Array Formulas
Cell Formula G3 {=SUM((A1:A10="jim")*(B1:B10="ben")*(C1:C10="alice")*(D1:D10))}
<tbody>
</tbody>
<tbody>
</tbody>
without CSE only the first number is returned, with CSE you get sum({30,15})
HXN | GEH | TZD | 11 | 0 | 45 | |
jim | ben | alice | 30 | 30 | 45 | |
WOA | ben | BAK | 23 | 0 | 15 | |
FXN | BUW | EIG | 71 | 0 | 15 | |
jim | JXP | alice | 4 | 0 | 15 | |
KPU | ben | RTH | 83 | 0 | 15 | |
JCH | LO | LZB | 50 | 0 | 15 | |
jim | ben | alice | 15 | 15 | 15 | |
jim | UBJ | ICO | 85 | 0 | 0 | |
jim | JCR | HIF | 54 | 0 | 0 |