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

### Excel Test

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.

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”

=VLOOKUP(D4,\$A\$15:\$B\$17,2,FALSE)

=C4*VLOOKUP(D4,\$A\$14:\$C\$17,3,FALSE)

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

=C4-F4

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

=IF(G4>200000,F4*0.025,F4*0.015)

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

=SUMIF(\$B\$4:\$B\$12,A22,\$H\$4:\$H\$12)

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

=COUNTIF(\$B\$4:\$B\$12,A22)

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.

=IF(G4>250000,IF(AND(D4>=20,E4>=0.05),10000,0),0)

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

***************************************************

***************************************************

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

In the commission file the formula is wrong.
=IF(G4>200000,G4*0.025,G4*0.015)
Otherwise they will quit from sales.

### Joseph

Simplier IF function:
=IF(AND(G4>250000;E4>=5%;D4>=20);10000;0)

### serg

Thank’s for the exercise!!

### S

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)

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!!!

### Sarah

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

### salahudheen

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