One of the first topics in Excel is absolute and relative references. If you copy cells with relative references, the references change, but absolute do not.

Cell B5 was copied to C5. As B3 was an absolute reference, it did not change. B4 was relative reference and it change to C4.

As there are two dollar signs in $B$3 it means that neither rows nor cells change when copying. B4 does not hold any dollar signs, therefore both rows and columns change when you copy.

However, you can have just one dollar sign in reference. $B3 columns do not get changed but rows do. B$3 columns change but rows do not.

To sum up:

$B$3 columns (letter) do not change, rows (number) do not change.

$B3 columns (letter) do not change, rows (number) change.

B$3 columns (letter) change, rows (number) do not change.

B3 columns (letter) change, rows (number) change.

In finance I have come across a case when semi absolute reference, with only one dollar sign, are useful.

A sales report, vertical axis represents products and horizontal sales areas. There are multiple records for example product C in North sales area.

With SUMIF function we can make a concise report.

For the function you need to have:

1. Range where you search a value, like product in B4:B17
2. The product like A which is searched in B4:B17
3. Sum range C4:C17, where you count sum if A is found in the same row.

The result is the sales volume for the product A in East sales area.

If you do like this, you need to do nine times for each product/sales area combination.

A formula can be written and copied.

The formula in I5 is =SUMIF($B$4:$B$17;$H5;C$4:C$17) . The range defining the product in data area should always be with absolute reference, as it should not change at any case. The criteria, which product is looked after in $B$4:$B$17, should have column as absolute and row as relative reference. When you copy the formula right, the column should not change as the product are one below another. If you copy downwards, the criteria should change to read the following product. The sum range in C$4:C$17 may not change if you copy downwards, then rows need to have an absolute reference. When you copy cell horizontally to right, the columns should change as another sales area is read.

When you write the formula in I5, you can just copy it till K7.