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.