# Formula to sum multiple values in one cell.

1. ## Formula to sum multiple values in one cell.

Hi guys, I hope you're in good health. I've a question about summing a number of rows, each with a cell with multiple values. It's a strange output file from an aircraft manufacturer that I need to cleanup.

As you can see, column H has multiple values corresponding to each panel in column G. I'd like to sum these values so I can see time for each task reference in column A.

It becomes issue in Cell 'G227', i'd like this cell to show 0.01 + 0.01 (0.02) and so on for rest of the column. But I need a solution that doesn't involve counting each cell, or splits the cells so that the reference in column A becomes 'detached' as it where. What I mean to say is, if I sort by column B time, I'd like the values that should 'stick' to Column A's reference to stay with it.

Is it possible to do this quicker than going through each cell manually? I've attached sheet.

2. ## Re: Formula to sum multiple values in one cell.

The only way, IMO, to do this quickly and without having to massage the data, and boy does that data need massaging, is to use a little "VBA" to create a custom function for you. This function would installed in the workbook, so the workbook becomes a .xlsm (macro-enable) workbook.

Then the new function would be entered into an adjacent column and then copied down the whole table. This could give you a "SUM" of column H values for each cell.

This function could be used on a single cell, or simply used to address and entire range of cells and give you a sum from the whole range. This means you could get a total in a single cell rather than having to create a whole column of values row by row.

Is a VBA solution acceptable?

3. ## Re: Formula to sum multiple values in one cell.

=SUM(--IFERROR(FILTERXML("<b><a>"&SUBSTITUTE(H2,CHAR(10),"</a><a>")&"</a></b>","//a"),0))

4. ## Re: Formula to sum multiple values in one cell.

That's just fabulous.

5. ## Re: Formula to sum multiple values in one cell.

Originally Posted by Bo_Ry

=SUM(--IFERROR(FILTERXML("<b><a>"&SUBSTITUTE(H2,CHAR(10),"</a><a>")&"</a></b>","//a"),0))
Hi Bo_Ry,

As JBeaucaire commented, fabulous! I will now dissect formula and see what each seperate function is. But's it's fixed my problem now.

JBeaucaire, I will endeavour to learn more about VBA. It's always been a black art to me!

There are currently 1 users browsing this thread. (0 members and 1 guests)