Excel Formula: Returning List Values Based on Criteria - TOM, BOB, JIM, and TIM

Excel Formula: Returning List Values Based on Criteria (TOM, BOB, JIM, TIM)

In this article, we will discuss how to use Excel formulas to return a list of values based on specific criteria. We will cover the following key concepts:

• Using the IF function to return values based on a single criteria
• Using the SUMIF function to return the sum of values based on a single criteria
• Using the SUMIFS function to return the sum of values based on multiple criteria
• Using the INDEX and MATCH functions to return a value based on multiple criteria
• Using the FILTER function to return a list of values based on multiple criteria

Using the IF function

The IF function in Excel allows you to return a value based on a single criteria. The syntax for the IF function is as follows:

Where logical\_test is the condition that you want to test, value\_if\_true is the value that you want to return if the condition is true, and value\_if\_false is the value that you want to return if the condition is false.

For example, let's say that you have a list of products and their corresponding categories in a worksheet, as shown below:

Sheet1:
B   C   D   E
1 Category TOM BOB JIM TIM
2 Product1 1   1   0   0
3 Product2 1   0   1   0
4 Product3 0   1   1   1
5 Product4 3   0   0   1

If you want to return a list of products that belong to the category "TOM", you can use the following formula:

This formula checks if the value in cell B2 is equal to "TOM". If it is, then it returns the value in cell C2. If it is not, then it returns an empty string. You can then copy this formula down to the other cells in the column to return a list of products that belong to the category "TOM".

Using the SUMIF function

The SUMIF function in Excel allows you to return the sum of values based on a single criteria. The syntax for the SUMIF function is as follows:

Where range is the range of cells that you want to test, criteria is the condition that you want to test, and sum\_range is the range of cells that you want to sum.

For example, let's say that you have a list of products and their corresponding categories in a worksheet, as shown below:

Sheet1:
B   C   D   E
1 Category TOM BOB JIM TIM
2 Product1 1   1   0   0
3 Product2 1   0   1   0
4 Product3 0   1   1   1
5 Product4 3   0   0   1

If you want to return the sum of values in column D for products that belong to the category "TOM", you can use the following formula:

This formula checks if the value in cells B2 to B5 is equal to "TOM". If it is, then it returns the sum of values in cells D2 to D5. The result of this formula is 1.

Using the SUMIFS function

The SUMIFS function in Excel allows you to return the sum of values based on multiple criteria. The syntax for the SUMIFS function is as follows:

Where sum\_range is the range of cells that you want to sum, criteria\_range1 is the first range of cells that you want to test, criteria1 is the first condition that you want to test, and criteria\_range2 and criteria2 are optional ranges and conditions that you can use to test additional criteria.

For example, let's say that you have a list of products and their corresponding categories in a worksheet, as shown below:

Sheet1:
B   C   D   E
1 Category TOM BOB JIM TIM
2 Product1 1   1   0   0
3 Product2 1   0   1   0
4 Product3 0   1   1   1
5 Product4 3   0   0   1

If you want to return the sum of values in column D for products that belong to the category "TOM" and have a value of 1 in column C, you can use the following formula:

This formula checks if the value in cells B2 to B5 is equal to "TOM" and if the value in cells C2 to C5 is equal to 1. If both conditions are true, then it returns the sum of values in cells D2 to D5. The result of this formula is 1.

Using the INDEX and MATCH functions

The INDEX and MATCH functions in Excel allow you to return a value based on multiple criteria. The syntax for the INDEX function is as follows:

Where array is the range of cells that you want to search, row\_num is the row number of the value that you want to return, and column\_num is the column number of the value that you want to return.

The syntax for the MATCH function is as follows:

Where lookup\_value is the value that you want to find, lookup\_array is the range of cells that you want to search, and match\_type is an optional parameter that specifies how you want to match the lookup value.

For example, let's say that you have a list of products and their corresponding categories in a worksheet, as shown below:

Sheet1:
B   C   D   E
1 Category TOM BOB JIM TIM
2 Product1 1   1   0   0
3 Product2 1   0   1   0
4 Product3 0   1   1   1
5 Product4 3   0   0   1

If you want to return the value in column D for the product that belongs to the category "TIM" and has a value of 1 in column C, you can use the following formula:

This formula uses the MATCH function to find the row number of the product that meets the specified criteria. The MATCH function returns an array of row numbers, and the INDEX function uses this array to return the value in column D for the specified row.

Using the FILTER function

The FILTER function in Excel allows you to return a list of values based on multiple criteria. The syntax for the FILTER function is as follows:

Where array is the range of cells that you want to search, include is the condition that you want to test, and if\_empty is an optional parameter that specifies what to return if no values meet the specified condition.

For example, let's say that you have a list of products and their corresponding categories in a worksheet, as shown below:

Sheet1:
B   C   D   E
1 Category TOM BOB JIM TIM
2 Product1 1   1   0   0
3 Product2 1   0   1   0
4 Product3 0   1   1   1
5 Product4 3   0   0   1

If you want to return a list of products that belong to the category "TOM" or "TIM", you can use the following formula:

This formula uses the FILTER function to return a list of products that belong to the category "TOM" or "TIM". The FILTER function uses the + operator to combine the two conditions, and it returns an array of values that meet the specified condition.

In this article, we have discussed how to use Excel formulas to return a list of values based on specific criteria. We have covered the following key concepts:

• Using the IF function to return values based on a single criteria
• Using the SUMIF function to return the sum of values based on a single criteria
• Using the SUMIFS function to return the sum of values based on multiple criteria
• Using the INDEX and MATCH functions to return a value based on multiple criteria
• Using the FILTER function to return a list of values based on multiple criteria

References

• IF function
• SUMIF function
• SUMIFS function
• INDEX function
• MATCH function
• FILTER function