In this problem, you must first complete the Sales table below by entering equations in cells C22, E22, F22, and G22 that can be copied and pasted down for each sales transaction. This will require the use of the vlookup function to find the price and the discount. The net column should be calculated as Total * (1 - Discount)... Next you will need to fill in the totals for the green shaded cells at the bottom of the table.
Once you have completed those tasks, the goal is to create a formula in cell D18 that will use the sumif formula to add up the net amount of sales to a particular customer... If the customer filter is blank (which can be tested in the if function by using B15 = "") then your formula should sum up the net amount of sales that satisfy the relationship specified by the Net Amount filter and the operator... For example, you may want to see what the sum is for transactions with a net amount in excess of $3,000. In that case you would type $3,000 in cell E15 and select the symbol > from the drop down box that I have already created for you in cell E16. If both filters are blank, then the cell D18 should just return the sum of the net column. To reiterate, your formula should first check to see if cell B15 is blank, if it is not, cell D18 should return the sum of all transactions for the customer ID listed in cell B15. If cell B15 is blank then your formula should check to see if cell E15 is blank, if it is not, your formula should sum up all transaction based on the amount listed cell E15 and the operator in cell E16. Finally if both Filters are blank, then the formula in cell D18 should just return the sum of the net column.
To accomplish this task, you will need to use the IF function and the SUMIF function as well as one of the two methods of concatenation to fill in the cell D18.
Solved by X. W. in 18 mins