This works because, the INDEX function returns a reference to the first cell, not the actual value. On the right side of the colon, we get a reference to the current row of the color column like this:
[@Color]// current row of Color
This is the standard structured reference syntax for "this row". Joined with the colon, these two references create a range that expands as the formula is copied down the table. So, we swap these references into the SUM function, we have:
SUM(--(B5:B5=[@Color]))// first rowSUM(--(B5:B11=[@Color]))// last row
Each of the expressions above generates an array of TRUE/FALSE values, and the double negative (--) is used to convert these values to 1s and 0s. So, in the last row, we end up with:
At the core, this formula has a simple pattern like this: = SUM ( first:current ) Where "first" is the first cell in the Total column, and "current" is a reference to a cell in the current row of the Total column. To get the a reference to the first...
At a high level, we using VLOOKUP to extract employee information in 4 columns with ID as the lookup value. The ID value comes from cell I4, and is locked so that it won't change as the formula is copied down the column. The table array is the table...
The Excel INDEX function returns the value at a given position in a range or array. You can use INDEX to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to...
The Excel SUM function returns the sum of values supplied. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.