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

Problem with SUMIF

0

Dear Team,

I have inserted sumif function for table to find out total sales for particular region. When I select North in the table, it gives me the subtotal and separate sumif table is not affected. Thats fine. But when I select East or West in the table, the separate sumif table's data automatically disappears. Hope I am clear,

Please help to resolve.

Thanks and Regards,

Akash Sharma

Answer
Discuss

Answers

0
Selected Answer

I wasn't able to reproduce the error you complain about and therefore think that the fault isn't in your worksheet but in your test. You may have copied your cells and thereby upset the ranges. If you copy your formula here or there you should employ absolute addressing.

=SUMIF($B$2:$B$11,$H$2,$D$2:$D$11)

(instead of =SUMIF(B2:B11,H2,D2:D11) which you used.)

Discuss

Discussion

Hi thanks for reply. Why we do absolute addressing? Why we need to insert dollar sign for all cells inside the formula? 
Akash Sharma (rep: 40) Aug 24, '18 at 12:19 am
There is no difference until you copy a formula. When you do relative addresses (without $) will change, absolute addresses (with $) will not. Try this:-
Enter something in A1. In B1 write =A1. Now copy B1 to the right. In C1 you will now have =B1, in D1 you will find =C1 etc.
Then repeat with [B1] = $A1. C1 will no be =$A1.
Now, In A2 write =A1 and copy down. You will have [A3] A2, [A4] =A3 etc.
You can prevent this by writing [A2] =A$1.
The rule is that you should make the column absolute if you wish to copy vertically and make the row absolute if you wish to copy horizontally, but there are exceptions depending upon the effect you want.
Please remember to mark my answer as "Accepted". Thank you.

Variatus (rep: 4889) Aug 24, '18 at 7:45 am
Add to Discussion


Answer the Question

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