add to total depending on cell value

[twofootgiant]

Hi Guys,

I am using a code which adds up the values of a number of cells and displays the totals on a seperate sheet.

I need to change this code so that it only adds numbers to the count if the word 'OPTION1' appears in column F 3 rows above that number....sounds complicated...its not...PLEASE CHECK MY ATTACHMENT, it will my requirements alot clearer.

Thanks

[antoka05]

You could use this code:

Please Login or Register  to view this content.

Regards,
Antonio

[twofootgiant]

thanks for your reply.

This works very well. How would I adapt this so that I could have the results for 'OPTION1' appear on one row...and the results for 'OPTION2' appear on the next row below?

[twofootgiant]

if i try this code, it adds all of them to the totals...can anyone see what Im going wrong here?

Please Login or Register  to view this content.

[antoka05]

You need to reset totals before making the second round.
If you have 'n' cycles to do (for instance from Option1 to Option100) there is a better code to write,
you don't have to repeat 100 times the code

Please Login or Register  to view this content.

Regards,
Antonio

[twofootgiant]

Hi Guys, Sorry to be revisiting this one, not sure if im allowed to do that or not...

The numbers to be found and totalled have now been moved to columns C:G instead of B:H. How can i modify the code below to allow for this change?

Please Login or Register  to view this content.

I Think the answer is going to be blindingly obvious....which is anoying as I have been sat here trying to work it out for ages now....

[Andrew-R]

The short answer is that you need to change the lines that say:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

The long answer is that this is a hideously inefficient bit of coding - just how many options are you running this for, because if it's any number at all or is ever likely to change then this should be rewritten to do things properly.

[twofootgiant]

Thanks for your reply,

What would be a more efficient method of doing this? I have about 20 different options. These are listed in the "Stats" sheet in columnA starting on A20.

Any help would be much appreciated.

Going back to your suggested code change, I am no longer getting any error message, but the values are not being copied into the "Stats" sheet. Any ideas?

[Andrew-R]

Is the text you're looking for still in column F, or has it shifted with the data to column G? My code assumed the latter, but if I was wrong then it will be looking to match the text in the wrong column, which would explain why nothing is being written to the Stats sheet.

As for a more efficient way, something like the following, perhaps ...

Please Login or Register  to view this content.

The key line is:

Please Login or Register  to view this content.

Which you can expand to look for as many options as you like without needing to change the rest of the code in any way at all, e.g.

Please Login or Register  to view this content.

The only other bit that you might want to change is:

Please Login or Register  to view this content.

Which sets the left hand column to sum, the right hand column to sum and the column to search for the option text. So in this example it's set to sum from column 2 (B) to column 7 (G) and search in column 6 (F). If you wanted to sum columns C to H you'd change it to:

Please Login or Register  to view this content.

Want to give that a try and see how it goes?