Addition with IF statements

[MrWhistle]

Hi,

I am having a problem with a formula in Excel.
I have a file with product details, prices, etc.. Each row has a product and I have 1 column with prices in each cell. I want to add up all these prices, except for some of those. These products are optional.
All optional items are labelled in the next row with a 1 for true and all other products are labbeled with 0.
The issue is that I do not know how to translate the logic into a formula.

Logic:
For E1 to E90
If Optional = 0 (false)
add Ex to total price

At the moment I am using the following code for a command:

Please Login or Register  to view this content.

Can I use a command that updates every time I update a value instead of clicking the button everytime?
Can I change the code to incorporate changes in the amount of rows? If products get added, I need to update the code manually at the moment.

I did not find similar problems using search, mostly because I did not know what to search for.

Thanks!

[mehmetcik]

You should be using the Worksheet Change event to trigger your Macro.

Right Click On Your Sheet Name At the Bottom Of Excel and Select View Code
Paste this code in the module that opens and close it.

Please Login or Register  to view this content.

[MrWhistle]

First of all, thanks.

I pasted your code and I think I understand what it does, but it does not work correctly for me.
It should find the bottom row with a filled cell in column F. Then it puts the sum one row below that in column E, right?

I get an error in this line:

Please Login or Register  to view this content.

The error states:
-2147417848 (80010108)
Method 'Value; of object 'Range' failed
And then Excel crashes.

[mehmetcik]

This worked perfectly for me.

Where did you paste the Macro?


Right Click On Your Sheet Name At the Bottom Of Excel and Select View Code
Paste this code in the module that opens and close it.

Please Login or Register  to view this content.

This also worked perfectly.

Please Login or Register  to view this content.

Maybe this version might be more stable:

Please Login or Register  to view this content.

[MrWhistle]

The more stable version worked for me. I have no idea why the first version did not work. Thanks for helping me this well.