SUMIFS by text string length

Values that meet one or more criteria can be added up with the SUMIFS function. The sum of values next to cells containing specific text, i.e., a sum based on text criteria, is possibly the most common use-case. The sum of values next to cells containing (non) specific text of a defined length (the defined number of characters in a text string) is far less common need – but still crucial in specific situations.   We can accomplish this task

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

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