Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Total Employees SUM showing as "0"

0

Dear Respected Team,

In sheet 2 I want total number of employees count client wise with respect to employee count given under sheet 1. Hence, if I delete one employee in sheet 1, automatically count should be less in sheet 2. I do not want any formula errors, I am confused with absolute and other referencing so kindly suggest correct formula.

Thanks and Regards,

Akash Sharma

Answer
Discuss

Answers

0
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.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login