Selected Answer
Hello Akash,
The formula you use is =SUM(Sheet1!C2:C20). This is called "relative addressing", meaning Excel calculates the address of the range relative to the location of the formula. If the formula is moved by one row (or column) the range also moves by a similar distance. That is why the formula in Sheet2!C3 is =SUM(Sheet1!C3:C21). This shift occurs automatically when you copy a formula. It's very useful.
At other times you don't want it, however. In that case you should use "absolute addressing". That would look like this: =SUM(Sheet1!$C$2:$C$20). Both rows and columns are specified absolute both for the first cell of the range and the last. The address will never change, whereve you copy it.
Actually, Excel is taught the wrong way around. On day one, when you start learning, all your addresses are absolute. That is the default. Then, as you become more proficient, some sollar signs should be removed depending upon what you want to achieve. If you want to copy a formula top to bottom, consider removing the dollar signs before the row numbers. Think about removing the dollar signs for the column IDs if you want to copy a formula left and right. All dollar signs should only be removed if you intend to copy a formula both up/down and left/right.
Of course, =SUM(Sheet1!C2:C20) will not sum up anything because the specified range has no numbers. You want to count. Use COUNT() to count cells with numbers. Since the cells in your range have text in them use the function COUNTA(). However, your intention seems to be even more complicated. I think you want to use COUNTIF().
=COUNTIF(Sheet1!$B$2:$B$39,$B2)
This formula will look at the range Sheet1!$B$2:$B$39 and count how many times the value of $B2 is found (that is "NCR"). As you intend to copy this formula the range to examine is specified absolute, as is the column B in Sheet2. But the row number in Sheet2 is specified relative. It's the only address which doesn't have a dollar sign because it is the only one you want to change as you copy down. So, in rows 3 and 4 you have slightly different formulas, automatically created by Excel during the copying process. =COUNTIF(Sheet1!$B$2:$B$39,$B3) and =COUNTIF(Sheet1!$B$2:$B$39,$B4). Accordingly, the formula looks at the same range in each row but counts different client names as specified in Sheet2!B2:B4.