Combining INDIRECT with ADDRESS

We’ve previously covered how we can use any function to generate a text that could be a valid cell reference, both column and row part of the address, and then use the INDIRECT function to convert that text to a cell reference. This process can be greatly enhanced by the ADDRESS function.   The ADDRESS function returns a text string that represents the address of a particular cell. Row, column, type of reference (locked or absolute), reference style (A1 or

Top 10 lists in Excel

When analyzing large amounts of data in Excel, the best approach often is to retrieve top (10) values. Consider the following example: Here, we have a table with the invoices from the 29th of January. There are 904 invoices for that day. Values in the invoice column are unique as those are invoice numbers. Values in the user column are not unique, as some users have multiple invoices on that day, and values in the team column are not unique

Nesting formulas, functions and conditional statements in the SUMIFS criteria

We’ve previously established what are the rules of writing criteria in the SUMIFS function: we can reference any cell we can enter any number directly in the function we can enter text directly in the function, nested inside of quotation marks [“”] logical test is nested inside of quotation marks [“”], with comparison operator coming first and a number second. The story however does not end there, and advanced users can do even more with the SUMIFS function.   We

Combining SUMIFS with INDEX MATCH

You will sometimes want to retrieve data from a table with values both in rows and columns and based on multiple (three or more criteria). There is, as usual, more than one way to do this. However, most of those ways require you to use something called array formulas – in essence, you will have to press Ctrl Shift Enter combination on your keyboard every time you want to calculate that formula. Combining SUMIFS with INDEX MATCH enables us to

Conditional calculations

IF functions for cell ranges In our previous section we’ve primarily dealt with testing conditions inside of a row, i.e. one or more cells in that row, and returning some kind of a result based on conditions in that row. While that is useful when we want to add something new to our rows, we will often have to deal with retrieving data based on criteria from whole ranges of cells, sometimes containing thousands of rows and columns. IF function