Microsoft Excel Conditional Functions with Data Validation:

In this Microsoft Excel lesson, it has shown the process of calculating conditional sums, and average based such as SUMIF, AVERAGEIF from a single criterion. The article is also representing how SUMIFS function are used to calculate conditional sums from multiple criteria. And, finally, along with Data Validation how a drop-down menu eases to visualize the SUMIFS value.

What can we learn:

1. Conditional calculation from a Single Criteria:

a. SUMIF Function,

b. AVERAGEIF Function.

2. Conditional calculation from Multiple Criteria:

a. SUMIFS Function.

3. In addition, we will learn few interesting Formulas such as:

a. UNIQUE

b. XMATCH

c. SORT

4. Finally, we will end up with Data Validation to create Drop-Down List.

Let’s assume, we have a company’s Total Expense Record in a tabular form which has attached below. The table contains six different columns which are Division, Category, expenses of January, February, March, and the Total Expense column. You can just copy the table to your MS Excel sheet.

From the table we will find the three Conditional Values(LOOKUP value as well) that are listing below.

I. Total Expense by Category

II. Average Expense by Category

III. And, Total Expenses for both Division, Category.

EE Trading 1st Quarter Expense Data Table:

Figure 1: 1st quarter expense data EE Trading

Beginning Job:

To synchronize the job till the end we will first name the respective columns.

Figure 2: Creating the Column Name

1. At first, we will give a name to the Division column. Just select from the first cell (East) of Division Column to the last cell (West) and tag a name as “com.div”. Please find it at where the cell number is used to mention.

2. Now, do the same thing both for the Category, and Total Expense columns as “com.category”, and “total.expense” respectively.

Total expenses by Category:

Here, we need to find out the Total Expense (Quarter One Expense) of each category from the company’s 1st quarter data sheet. There will be two new columns for the calculation.

As mentioned earlier, different types of categories are mentioning on different rows in Category column. We need to extract single variable of each category from that certain column.

1. To get the unique value of the Category Column write the Function in a different cell of same sheet as; = UNIQUE(com.category), and press enter.

Figure 3: UNIQUE Value of Category Column (UnSORTED)

2. Now, just add SORT before the previous formula; =SORT(UNIQUE(com.category)), and you have the SORTED UNIQUE column as given below.

Figure 4: Sorted UNIQUE Column

3. Afterwards, to calculate the Total Expense of each Category apply the Function at the first cell of newly “Q1 Expense” column; =SUMIF(com.category, I22, total.expense), and hit enter. Afterwards, copy the newly total expense value , and paste it to the remaining cells. Please find below the explanation of the formula.

a. com.category is the Column Range,

b. I22 is our Criteria cell (Advertising in Figure 5) that we want to calculate for total expense,

c. And, lastly, total.expense (Total expense Column) is or Sum range.

So, the new table of Q1 expense of each category has bloomed like below:

Figure 5: Total Expense of 1st Quarter by Category

Apart from the above procedure we can also select the respective Cell range instead of com.category, and total.expense. The formula interface is looked like below.

Figure 6: Function “SUMIF” Interface

AVERAGEIF Function:

Here, we will calculate the AVERAGE Expense of each category for the 1st Quarter by applying the Formulas listing below:

i. UNIQUE(com.category) — to get the unique value of categories as done previous.

ii. SORT(UNIQUE(com.category)) — for sorted the column

iii. AVERAGEIF(com.category, I13, total.expense) — to get the “Average Expense”.

1. Write down the Column Name as;

Figure 7: Column names for Average expense calculation

2. Bring the UNIQUE value from the Category column with the Fx of; =UNIQUE(com.category)

3. Sort the Column by applying; =SORT(UNIQUE(com.category))

Figure 8: SORTED UNIQUE Value of Category

4. Write down the Formula of AVERAFEIF under the newly column of “Q1 Avg Expense” as: = AVERAGEIF(com.category, I13, total.expense), and hit enter. Again, copy and paste remaining cells of the column. The newly calculated table will be look like below;

Figure 09: Average Expense of Each Category of 1st Quarter

or, open AVERAGEIF Fx Interface, and put the needful as below:

Figure 10: AVERAGEIF Formula Interface

Conditional Sums from Multiple Criteria:

In this valuable lesson, we will learn how to calculate Conditional value from the multiple criteria as well as some interesting calculations:

i. SORT

ii. XMATCH

iii. SUMIFS

iv. DATA VALIDATION

v. Creating Drop Down List

We will find here Total Expenses of a Certain Category of a Certain Division.

1. Add a name as “selected.category” for the “Selected Category” column that we have found above. For this, select cell from “Advertising to Telephone”, and give it a name as; selected.category.

2. Afterwards, listing and sorting the Division Variables from the “Division column” by applying the Formula of; =UNIQUE(com.div), sort it by =SORT(UNIQUE(com.div))

Figure 11: Selected Sorted Division

3. Now, put the XMATCH Formula; =XMATCH(J48,J42#), and we will find the following information.

Here, J48 = East (Write down it in a Sperate cell as showing on the figure Left), J42# = East to North cell (Select East to North). We will find the East value as 1, as it is at the first selected cell.

Figure 12: Selected Sorted Division, Sort By, XMATCH

4. This time, create the Selected Category as; = selected.category, and hit Enter. Sort it by; =SORT(selected.category).

Figure 13: Selected Category for SUMIFS

5. Now the Formula; =SUMIFS(total.expense,com.div,$L$60,com.category,O53), hit “enter” (under the newly created “Total Expense” beside the “Selected Category” column). Copy, and paste the remaining required cells to the downwards. We can also use the formula interface as below;

Figure 14: SUMIFS Fx Interface

Explanation of SUMIFS function:

i. In the SUMIFS function, Sum_range = total.expense column (or, select the entire cell range of Total expense)

ii. Criteria_range 1 = First criteria which is com.div (or, the Division Column range)

iii. Criteria 1 = “East cell” that we write down earlier for XMATCH

iv. Criteria_range 2 = Second Criteria which is com.category (or, select entire category column range)

v. Criteria 2 = “First Cell” of the Selected Category (Advertising)

Therefore, we get the Total Expense of the Categories of East Division.

Figure 15: Total Expense of Categories of East Division

Now, at the same cell write down the North or, South or, West and hit enter, you will find the respective Division data.

Figure 16: Total Expense of Categories of North Division

Amazingly, number 2 has appeared at the XMATCH cell as North is standing on number 2 position at the Selected Division Column.

Data Validation:

a. Drop down menu: From Menu bar select “Data menu” — Data validation/Setting/Validation Criteria/Allow/Select “List”/Source — Cell Range

Figure 17: Data validation interface

For this, we will get a cell like this. If we click on the drop-down icon, we will find the four divisions mentioning on the list. But nothing will happen till then.

b. Last but not the least, go to the cell mentioning East; click on the equal (=) Sign, go to the adjacent cell of the drop-down sign, and hit Enter.

c. Now we can select the one of four divisions from the drop-down list.

A complete table pic has given below.

Figure 18: Data validation

Ending Summary:

For the betterment of all, a table of the full process has attached below:

Figure 19: Process Summary.