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 :

=SUM(INDIRECT(ADDRESS(ROW(A5),MATCH(A3,A5:D5,0))&":"&LEFT(ADDRESS(ROW(A5),MATCH(A3,A5:D5,0)),2)&"$"&20))

(OR)

=SUM(INDIRECT(ADDRESS(ROW(A5),MATCH(A3,A5:D5,0))&":"&ADDRESS(20,MATCH(A3,A5:D5,0))))

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 .

LEFT(ADDRESS(ROW(A5),MATCH(A3,A5:D5,0)),2)&"$"&20) 
(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

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts