$ in SUMIF function not understood

Question

We have an Excel workbook that was upgraded from Excel 2003 to Excel 2010. One of the worksheet called Calc_Sheet has columns named A,B,C,D and a column called Calc that has the following formula:

=SUMIF($A:$A,$A2,$B:$B)

The above formula is for the cell in row two of the Calc column. Likewise, for cell in row 3 of the same column the formula displays as:

=SUMIF($A:$A,$A3,$B:$B)

...and so on.

Our workbook has some worksheets that also have the names as A,B,C. I don't know if the above formula has anything to do with the worksheets A and B or it is referencing the columns A, B of the worksheet Calc_Sheet.

I know what a SUMIF function is as explained by Microsoft here with examples.

But all the examples there are using no $ sign. So, what the above formula translate to?

Thanks.

user2140261 user2140261 · Accepted Answer · 2014-01-17T20:41:32

The way your formula is written just means that if you drag that formula to the left or the right, it will still look at columns A and B, If you remove the $ then when you drag the formula to the left or the right the entire formula will change what columns it is looking at. The $ represent Absolute References, in other works whatever following the $ will not change no matter where you place the formula.

The $ will also lock in the rows if seen before the Row number also.

A1 = Row and Column Will change when the formula is dragged around
$A1 = The Row number will change but not the Column  reference
A$1 = the column may change but the row will not
$A$1 = The Formula will always reference `A1` even when filled down or across.

NOTE: Even with Absolute Referencing on, excel will modify the formula when columns are either inserted or deleted.

For an example see Here

Or

For more information please see Here Or Here

The SumIf Function

the sytax of the SumIf Function is:

SUMIF(range,criteria,sum_range)

Following this, syntax, You are creating a a Logical Statement (True or False) where the cells in the range specified, are compared to the criteria you provide.

If no operator is supplied but only a Number or Text value then the cells in the range provided will have to equal the criteria.

You can also use operators like the < and the > symbols.

Once you have the Range you want to test and the criteria by which you would like it tested the last part of the function is the sum_range here you give the formula a range of cells that it can actually sum.

The function will sum the cells in the sum_range where the cells in the range from the first p[art of the syntax meet the criteria.

=SUMIF($A:$A,$A2,$B:$B)

So given your sample above. It is giving the sum of all the Number in B:B where the cell in A:A on the same row is equal to the value in A2.

$ in SUMIF function not understood

1 Answers