Adding numbers from an array into a single cell

[realdemigod]

Hi,
I have list of values(numbers) on a particular column (col. D in the xl attached) but this list varies for several scenarios. The good thing is the list of numbers are spaced at constant intervals. I'm able to capture the list of numbers into an array but struggling to add all the numbers in the list i.e., adding all the numbers stored in this array from the list into a single cell.

Could somebody please help me on how to get this done?

P.S. I need to save the list of numbers into an array.

[pjwhitfield]

Sorry youre going to have be a bit more specific, your attachment simply has 3 numbers in it, no explanation or anything.

Please explain exactly what youre trying to achieve and ideally, amend the attachment to show examples ie show what the data is, where you want it putting etc.

[realdemigod]

Hello,
Sorry for not being clear. I have updated the excel. In it there are tables and I need to get the sum of the values of the 'Benefits' row into a cell. So in this case 'G8+G17+G26+G35' goes into one particular cell and similarly other columns (H to Z). As these tables are populated by another macro, the way I'm currently doing is storing G8,G17,G26,G35 in an array but I'm struggling to get 'G8+G17+G26+G35' into one cell that would fit into another table.


Please ignore the poisition of this cell, which has the 'G8+G17+G26+G35' value. I need help with the summation of numbers stored in the array

[pjwhitfield]

Thank you that definitely is more descriptive. Im still not understanding the question though (its Friday and my brain is already in Weekend mode!).

I understand that you need to total up all of the individual columns Benefit values however you are talking about arrays, is there some other reason you need the data in arrays or is the array just the result of a previous attempt at solving this? Im also unsure what you mean by "I'm struggling to get 'G8+G17+G26+G35' into one cell that would fit into another table."

[jindon]

For the data provided.

Please Login or Register  to view this content.

[realdemigod]

Same here with my brain! . The reason I'm saving the values G8, G17, G26 and G35 into an array is because those tables are generated from a macro depending on the number of Use Cases (1 Use Case = 1 Table) from another sheet. And the use cases vary from sheet to sheet and I don't know how many tables will get generated from each sheet. So the only way I thought is to store the benefits from each column from each table into an array. And I need sum of the values like G8, G17, G26, G35 and so on..

"I'm struggling to get 'G8+G17+G26+G35' into one cell that would fit into another table." - This summed value will be populated in another table. If I get the sum I can manage putting it anywhere.

Hope I didn't confuse you!

Edit:

Hi jindon,
Thanks for the code but little confusing for a novice like me If you don't mind could you please look at the v2 excel I have attached and from the challenge I shared above, could you please modify the code

Thanks

[jindon]

OOps, I've read your question wrong...

Where do you want the result?

[realdemigod]

No problem jindon, anywhere may be G40. So G40 = Sum(G8,G17,G26,G35)

[jindon]

Try

Please Login or Register  to view this content.

[realdemigod]

Thanks jindon! The code works like magic :D. If you don't mind could you tell me what it exactly does. I'm unable to get my head around it

[realdemigod]

Hi jindon, in my larger worksheet this is not working properly. I have attached the sheet. I need to have F98= Sum(F32, F42, F52. F62) and similarly for columns G to Y [G98= Sum(G32, G42, G52. G62).. Y98= Sum(Y32, Y42, Y52. Y62)].

Very sorry that I missed out an important part that there is another table Rows 67 to 89 and this table needs to be ignored for calculating the sums from above tables.

Could you please modify your code

[jindon]

Is it fixed like this?

Please Login or Register  to view this content.

[realdemigod]

That was great, it worked . Thanks!

However I don't know how many cells I need to add, in this case there are four tables hence '=sum(f32,f42,f52,f62)'. It could vary from one to many tables before the 'Costs of Development and Operation' table. But I have the row numbers, is there any way I can generalise this sum function with some counting variable?

[jindon]

Be careful, you have trailing space in B98 and the code will look for that row.

Please Login or Register  to view this content.

[realdemigod]

Domo jindon! Let me spend sometime on this code and will get back to you if I have any confusion.

[realdemigod]

Thanks jindon, the code is working perfectly but could you please tell what it does exactly.

[pjwhitfield]

If Im reading you correctly then you dont necessarily know how many tables you're going to have and therefore cant just do a standard sum, if so then you could just use SUMIF

eg to get the total of the column G entries use the formula:

Please Login or Register  to view this content.

Which basically sums the items in column G whenever theres the word "Benefits" in column B

EDIT: To ensure your "totals" table isnt included slightly change the word Benefits in that table you could even add a trailing space or Underscore.

[realdemigod]

Hello pjwhitfield, I'm very sorry. I didn't realise that you replied to this thread. Thanks for your help.