Excel Related Exercise/Test (Vlookup, Sumif, Countif function), The Answer

Excel Test

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials

Today, I am following up on last week’s excel quiz where I challenged all of you to take part in a little test of your understanding of some key excel functions, you can do it here if you have not done so.

Step #1-Download this spreadsheet

To remind you, here are the steps with the answers! This is what the spreadsheet looked like to start off:

Step #2-Using one of the functions described, get the correct number in column “E”. It should be one formula that you can then drag down that will use the correct %. Then, using the chart at the bottom left and the same function, fill out column “F”



Step #3-Calculate the amount to be financed (Selling price – down payment) in column G


Step #4-Fill out the commission to be paid out to each agent. It should be calculated as follows. They get paid only on the amount to be financed. They actually get paid:

2.5% if that amount is over or equal to $200,000
1.5% if it is lower than $200,000


Step #5-Using one of the formulas, calculate the correct commission for each seller in cells B22-23


Step #6-Using another one of the formulas, count how many sells each agent made in C22-23


Step #7-The branch has an extra bonus where the bank manager will receive $10,000 for any loan that is over $250,000 with a loan term (amount to be financed) of 20 years or more AND an interest rate of 5% or more. Please enter a formula in I4 that you can drag that will give you the bonus amount for each loan.


That’s it:) Here is what the answer looks like:

You can also download the answer here:)


Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


8 Feedbacks on "Excel Related Exercise/Test (Vlookup, Sumif, Countif function), The Answer"


In the commission file the formula is wrong.
It should read:
Otherwise they will quit from sales.


Simplier IF function:


Thank’s for the exercise!!


Yes the formula for commission is wrong. It should be IF(G4>=200000,2.5%*G4,1.5%*G4)

Why? Because
– They get paid only on the amount to be financed (which is column G and not F)
– 2.5% if that amount is over or equal to $200,000 (so equal to sign is required)

fjr advisors llc

That is really attention-grabbing, You’re an overly skilled blogger. I have joined your feed and look ahead to searching for extra of your magnificent post. Additionally, I’ve shared your website
in my social networks

ritika arora

excellent exercise!!!


Great exercise- best I’ve found so far! Thanks


Can I have the question for this pls? I dont understand the commission calculation formula (sumif).


Please Leave a Comment!

Please note: Comments may be moderated. It may take a while for them to show on the page.