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 :




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 .

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts