However, in the current state, the result of SUMPRODUCT will be zero because the TRUE and FALSE values will be treated as zero. The first array contains the TRUE FALSE values that result from the expression A2:A6TX, and the second array is the contents of B2:B6.Įach item in the first array will be multiplied by the corresponding item in the second array. See more below.For the sum example above, here is a virtual representation of the two arrays as first processed by SUMPRODUCT: array1array2FALSE75FALSE100TRUE125FALSE125TRUE150Each array has 5 items.
This is a common trick used in more advanced Excel formulas to coerce TRUE and FALSE values into 1s and 0s. SUMPRODUCT for conditional sums and countsAssume you have some order data in A2:B6, with State in column A, Sales in column B:AB1StateSales2UT753CO1004TX1255CO1256TX150Using SUMPRODUCT, you can count total sales for Texas (TX) with this formula.Ī2:A6 TX ),B2:B6 )Note: Dont be confused by the double-negative.
Because it will handle arrays gracefully and without complaint, you can use it to process ranges of cells in clever, elegant ways (see links to formula examples on this page).To illustrate how SUMPRODUCT works, here are a few common examples.
0 kommentar(er)
