Excel Tips and Tricks

Count unique values Excel

This article shows how to count unique values Excel formulas SUMPRODUCT and COUNTIF. The SUMPRODUCT function can handle arrays without actually using array formulas, and this allows evaluating the formulas in each cell of an array. Syntax =SUMPRODUCT( 1 /...

How to get last match

This article shows How to get last match by using MAX, MIN, IF and ROW functions. Excel's array formula ability to evaluate formula for each cells in an array is key factor in this case. Syntax { =MAX( IF( data = search value, ROW( data ) - MIN( ROW( data...

How to find closest match

This article shows How to find closest match by using INDEX, MATCH, ABS and MIN functions. Excel's array formula ability to evaluate formula for each cells in an array is key factor in this case. Syntax { =INDEX( return array,      MATCH(         MIN(ABS(...

How to sum every N rows

You can sum tables, columns, but what how do you add up every third row? In this article, we're going to show you how to sum every n rows using the SUM and the OFFSET functions. The OFFSET function's ability to return range is the key in this case. Syntax...