Using SUMIF, the formulae to get data for August will be as follows:

​

=SUMIF($A$2:$B$7,A13,$F$2:$F$7)

​

where $A$2:$B$7 represents the product name column, A13 represents the variable which is product B in the report table,  $F$2:$F$7 represents the column name ‘August’ containing the August sales figures. In order to get July sales figures, sum range should be changed to $E$2:$E$7 to represent the July Column. Likewise to get another months sale, sum range has to be manually changed to select the related column in the table. Instead of manually changing the sum range, INDEX and MATCH function could be clubbed together with SUMIF function so that by applying formulae in one cell and by merely dragging the formula, it can be extended to other cells.

 

INDEX function

​

INDEX function is commonly used to return the value in a given row number and column number. In addition to this, INDEX function also returns the reference to a cell depending on the row number and column number given. Syntax of INDEX function is as follow:

​

INDEX(array, row_num, column_num)

​

By making the row number or column number as zero, INDEX function can be used to return the reference to a column or row respectively in a table.

​

In the above example in order to get the reference to the column August, formulae will be :

​

=INDEX($A$1:$G$7,0,6)

​

Here cell range $A$1:$G$7 refers to the whole table. Row number is given as zero and hence ignored.  Sixth column in the range refers to $F$2:$F$7. Since row number is zero, the whole column is returned.

​

As we have seen in the above formula, by changing the column number in the INDEX function, different column ranges can be selected. 

 

MATCH function

​

MATCH function as we all know returns the position of an item in a row or column. Syntax of MATCH function is:

​

=MATCH(lookup_value, lookup_array, [match_type])

​

Column number 6 given in the INDEX formula above to get August month reference can be alternatively obtained using the MATCH function. For this look up value will be ‘August’ and look up array will be the reference to the heading row in the table which is $A$1:$G$1. Match type can be any value such as 0, 1 or -1. In order to get the exact match, 0 is to be given and the formula will look like:

​

=MATCH(“August”, $A$1:$G$1, 0) which will return the value 6

​

By clubbing all the functions, the formulae to be written in cell C20

​

=SUMIF($A$1:$A$7,$A12,INDEX($A$1:$G$7,0,MATCH(B$11,$A$1:$G$1,0)))

​

where $A$1:$A$7 represents the product name column,

​

$A12 represents the variable in report table (Table 2) for which sales figures are to be obtained,

 

INDEX($A$3:$I$16,0,MATCH(C$19,$A$3:$I$3,0)) is used to get cell reference for August month in the data table (Table 1) which will return range $F$1:$F$7.

​

The output table (Table 2) will look like this: