Using SUM & CHOOSE Functions To Calculate Total Sales

Welcome to Excel Jet Consult blog post. In this post we want to learn how to use the rarely known lookup family function – CHOOSE. We will wrap the CHOOSE formula inside sum function.

CHOOSE Function chooses a value to perform from a list of values based on index number

CHOOSE Syntax                =CHOOSE(index_num, value1, [values2], …)

In the caption below, we want to calculate the Total Sales for the transaction generated by each of the Account Manager

To calculate the Total Sales: In cell B4, execute the =CHOOSE(B3,B7:B20,C7:C20,D7:D20,E7:E20,F7:F20,G7:G20,H7:H20,I7:I20,J7:J20,K7:K20,L7:L20,M7:M20)

Click Enter. The CHOOSE formula delivered #VALUE! Error as seen in the caption below

All we need to do is simple wrap the formula inside SUM function. To do that, type in SUM with open parenthesis after the equal sign and apply close parenthesis at the end

Click Enter. The Total Sales for January which represent index number 1 in cell B3 is ₦158,069.32

When we replace the index number 1 in cell B3 to 12, the SUM & CHOOSE formula will return the value for Dec. See below

That is how to use the SUM and CHOOSE function. Hope you enjoyed the tutorial.

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *