Kind-of extended sumif
To combine logicals, * corresponds to AND and + corresponds to OR. Thus
(E2:E10=78)
returns an array with TRUE (1) in positions correspond to values of 78
in E2:E10 and FALSE (0) elsewhere
(E2:E10=78)+(E2:E10=45)+(E2:E10=700)
returns an array with 1 in positions that correspond to values of 78 or
45 or 700 in E2:E10 and 0 elsewhere. Hence
=SUMPRODUCT((E2:E10=78)+(E2:E10=45)+(E2:E10=700),E 2:E10)
sums values from E2:E10 that are equal to 78 or 45 or 700.
Jerry
Andrew < wrote:
I tried some of the above SUMPRODUCT array formulas in a single column
but did not have much luck. Maybe I'm doing it wrong.
These are two that seem to work as multicriteria SUMIF and COUNTIF
formulas within a single column of numbers.
=SUMPRODUCT((E2:E10=78)+(E2:E10=45)+(E2:E10=700),E 2:E10)
Answer = 823
=SUMPRODUCT((E2:E10=78)+(E2:E10=45)+(E2:E10=700))
Answer = 3
Note that all three numbers, 78, 45 and 700 appear within the E2:E10
range. Changing any of the three numbers within the range will effect
the answer (obviously?)
Any feedback or suggestions? Never too old to learn something new :)
|