windows 7 starter serial key Use SUMPRODUCT to fin

[artists266]

Today’s author is Charley Kyd, an Excel MVP,microsoft office pro x86, who describes how to use the SUMPRODUCT function to find the last sequential item in an Excel list. worked with spreadsheets in business since 1979, and has written books on spreadsheet use for McGraw-Hill and Microsoft Press. You can find more useful tips and posts from Charley and other Excel MVPs at his website, ExcelUser.com. you have a Sequential List of items, as in the second table here. And suppose you want to show the date of the first and last entry in the list. How would you do it? question isn't limited to dates,microsoft windows 7 home premium key, by the way. The Sequential List could display numbers or text instead. How would you write the formulas used in the range B4:C6 to return the dates of the first and last items? Excel users could quickly write a formula that returns the first date for each item in the list. They would use an INDEX-MATCH formula like this for the cell shown: =INDEX(Date,MATCH(A4,Item,0)) INDEX-MATCH won't help us to find the last occurrence of each item in the list. This is because MATCH (with a match-type equal to zero) returns the first item in an unsorted list. There's no version that returns the last item. as the title of this short article suggests, Excel's SUMPRODUCT function provides just the power we need. Here's the formula for the cell shown: =INDEX(B:B,SUMPRODUCT(MAX((Item=A4)*ROW(Item)))) if you use SUMPRODUCT a lot, you might find it difficult to understand this formula at first glance. So let's take it one step at a time... works with arrays of data. But it's not limited to mere multiplication, as the "PRODUCT" part of the name implies. In fact, here's how I tend to think of SUMPRODUCT: = (do stuff as though the formula were entered as an array, and then return the results) that's not a very scientific explanation, but it's not a bad rule of thumb. inside the SUMPRODUCT formula above, let's start with: (Item=A4) the function compares the value of each cell in the Item range (cells A10:A19) to the text in cell A4 ("Hats"). In its memory, Excel sets up an array with TRUE where an Item equals "Hats" and FALSE where it doesn't. (Item=A4)*ROW(Item) I've told the function to multiply the row number for each item by the corresponding value in the TRUE/FALSE array. evaluates as 1 in a formula and FALSE as 0, this product returns an array of numbers. Most of the numbers equal zero, where the Item doesn't equal "Hats." But where an Item does equal "Hats," the array contains the row number where the item is found. next step: MAX((Item=A4)*ROW(Item)) is simple. MAX returns the value for the largest row number in the array of numbers and zeros,windows 7 activation, which is where "Hats" occurs last in the list. the next step: SUMPRODUCT(MAX((Item=A4)*ROW(Item))) is merely the function that tells Excel to handle the earlier calculations as arrays. We use this section of the formula to return one simple number: The number of the row in your worksheet where "Hats" appears last. the whole formula again: formula now should start to make more sense. There's only one step left to go: [the last row number for "Hats"]) INDEX is looking at all of column B in the spreadsheet. Among other information, column B includes the date information in the Sequential List. INDEX returns the date found in the row number specified by the SUMPRODUCT function. And that row number is where the last occurrence of "Hats" can be found. you might consider a variation of the formula, a variation that eliminates one risky aspect of this approach: The formula shown above for cell C4 references an entire column. entire column is a problem in this case because if you cut and paste the Sequential List to some other location,buy microsoft office 2010 Standard, the formula still will reference column B, but the list won't be in it. And the formula will fail. modified formula corrects this problem: =INDEX(Date,SUMPRODUCT(MAX((Item=A4)*ROW(Item)))
-ROW(Date)+1) referencing column B, this formula references the range named Date. To get the index value we need for this range,windows 7 starter serial key, we subtract the row number of its first row, and then add 1. (Because the Item and Date ranges always must be in the same rows, we also could have used the first row number of the Item range.) formula in cell C4 and then copy the range B4:C4 down two rows. When you do so, the new formulas will return the first and last occurrences of Shoes and Socks, respectively. <div