What are Excel Tables? Excel Tables, or just Tables, are tables superimposed over regular cells. Excel Tables are assumed to be containing structured data. Both tables and individual table columns are named and intended to be referenced in formulas by those names. As they are typically used to load structured data from an external source into Excel, and that external data is typically accessed from Excel using the Data ribbon and then loaded into Table, they are also sometimes called …
Tag: SUMIFS function
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 a far less common need, but still crucial in specific situations. We can accomplish this …
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: This table contains all of the invoices from the 29th of January. There are 904 invoices for that day. The values in the invoice column are unique as those are invoice numbers. The values in the user column are not unique, as some users have multiple invoices on that day, and the values in the team column are …
SUMIFS criteria: formulas, functions and conditional statements
We’ve previously established the rules for writing criteria in the SUMIFS function: we can reference any cell we can enter any number directly into the function we can enter text directly in the function, nested inside quotation marks “” a logical test is nested inside of quotation marks “”, with the comparison operator coming first and a number second. We will further expand on the topic in this article. Formulas and functions can also be nested in SUMIFS criteria. If needed, …
Combining SUMIFS with INDEX MATCH
Using the SUMIFS function, we can sum all of the values in a defined column (or row) that meet one or more criteria. When SUMIFS is combined with INDEX MATCH, that sum range doesn’t have to be defined anymore; it is now rather specified in the function arguments. By combining SUMIFS with INDEX MATCH, we can then sum all of the values that meet multiple criteria in different rows and columns and do this in a simple way, avoiding complex and …
Lookup with unique identifiers
INDEX function The INDEX function returns the value from a range of cells based on the row and column specified in the function arguments. We are simplifying here: the INDEX function can actually return values from one or more arrays, i.e., a broader term for lists of data that includes cell ranges. But the most common use is with cell ranges, as that is the typical way of structuring data in Excel. The syntax of the INDEX function is …
Conditional calculations
IF functions for cell ranges Using conditional statements, i.e., the IF function, we can test conditions and perform actions if conditions are met. This IF-THEN-ELSE conditional processing is useful when we want to add something new to our rows. However, it is not really appropriate for retrieving data from or about whole ranges of cells, sometimes containing thousands of rows and columns. For example, in order to sum the incentive paid out to “green” team members, we had to …