Wednesday, 17 June 2015

How to Find Sum or Total of a Specified Column or Field based on Match Criteria

Excel Function to Find the Sum or Total of a Specified Column or Field based on Match Criteria
Suppose we have a data table with Columns/Fields as Sales_Period, Jan, Feb, Mar as shown below.

Now I took Month names in a drop down at  range \$A\$3. When I select a Month name from the drop down , I want to see the Sum/Total of that Month at range \$B\$3.
This we can get by using the following Combo Formula :

(OR)

How this Formula works :
ADDRESS(ROW(A5),MATCH(A3,A5:D5,0)) will returns the address({"\$C\$5"}) of the cell where the \$A\$3 value is found .

(OR)
ADDRESS(20,MATCH(A3,A5:D5,0)) will returns the address {"\$C\$20"} , which the maximum range size that we want to Sum.

INDIRECT({"\$C\$5:\$C\$20"}) will converts the range in the String format to a Normal Excel Range., as \$C\$5:\$C\$20.

Finally , SUM( \$C\$5:\$C\$20 ) will gives the Sum for the Month selected from \$A\$3.

Note :
This formula seems to be complicated and less use full , as now a days Pivot Tables came into picture , which shows the Grand Totals by Rows and Columns.
But any how , this is one of Technique to get Sum by Column Match Criteria.
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------