Question

SUMIFs formula to find the data below. The data set is too large, I know how to write two different formulas to find revenue and quantity, but I need to create one function that can be copied across and down the table

Table 1 - Total Revenue and Quantity Sold by Country
Retailer country	Revenue	Quantity
Mexico
Netherlands
Singapore
Spain
Sweden
Switzerland
United Kingdom
United States	$650,810,961	11,841,817

Answer 1

To copy the SUMIF formula down and across, we will need that the data at the source file is arranged in the same way as shown in the report. Please consider the following steps :

1.Since the Revenue & Quantity are arranged in two consecutive columns in the summary sheet, they are arranged in two consecutive columns of the data sheet also.

2. The formula to be used should be such that when copying across, the SUMIF parameter (" Retailer Country") column should not change both in the Data sheet as well as the Report sheet.

3. The formula to be used would be as under:

BG A B C D E 2 Table -1 Total Revenue and Quantity Sold by the Company 4 Retailer Country Revenue Quantity 5 Mexico =SUMIF($L:$L, $A5,N:N) 6 Netherlands 7 Singapore 8 Spain 9 Sweden 10 Switzerland 11 United Kingdom 12 United States $65,081,091 $11,841,817

It has been assumed that " Column L" in the data sheet contains the " Retailer Country ". Once we use this formula, we affiix the "S" sign against the parameters which we want to remain unchanged.

If we want a column to remain unchanged, we would use the $ sign only against the column no.

example

1. =SUMIF(L:L,$A5,N:N) makes the column "A" fixed, it would not change if we move across to the next column.However if we move down to next cell it would change and would appear as =SUMIF(L:L,$A6,N:N)

2. If we want a cell to remain unchanged, we will would use $ sign both against the column no, and the cell no.

In the above mentioned example, if we use the formula as =SUMIF(L:L,$A$5,N:N),the selection parameter would not change if we move across or up-down.

We would use the formula as given in example 1 because the selection parameter (Retailer Country) changes if we go down the column but would not change if we go across.