Summing Cells that have more than one value?

[t0mrogers]

Is there a formula that will SUM all values of a single cell.

For example:

If I have a cell that contains more than one value, say 100 and 200 in same cell. Is there a formula to SUM these 2 values in another cell?

Thanks in advance

[Glenn Kennedy]

You did not tell us what the separator is.

This formula:

=SUMPRODUCT(--MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),99*(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)+1,99))

is set up for CHAR(10) - new line - separators.

[billgyrotech1]

It would be better to split the cells so that one value is contained in each cell.

[Glenn Kennedy]

Or if it is a space....

=SUMPRODUCT(--MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)+1,99))

[t0mrogers]

I am using a PDF converted to Excel file as a feed sheet so I can just copy and paste the sheet in once converted, so can't really split the cell unfortunately.

[billgyrotech1]

Text to columns deliminated can do that

[t0mrogers]

"1,217
UK & Ireland
663
Web UK & IE"

The cell I want to sum contains the above.

[Glenn Kennedy]

Now that is a totally different question, isn't it. In Post 1 you never mentioned that there was text in the cells as well.

You will need VBA for that.

[Glenn Kennedy]

Try this:

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


=SUMnUMBERS(A1)

[t0mrogers]

I basically want to be able to paste my PDF to excel sheet into a back sheet so the data pulls through to the front sheet automatically but SUMS the multiple values from one cell from the back sheet into 1 cell on the front sheet.

Does this make sense?

[billgyrotech1]

Glenn has the reins I opt out.

Good luck

[Bo_Ry]

Text at A1

Please try at B1 and press Ctrl+Shift+Enter

=SUM(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)," ")," ",REPT(" ",100)),ROW(INDIRECT("1:20"))*100-99,100),))

[Glenn Kennedy]

Very nice, Bo. I can't add rep for you 'cos of forum restrictions...

[t0mrogers]

I have attached an example workbook.

I need the formula to SUM the 2 numbers in cell A29 on Front Sheet and display the SUM in cell D3 on the back shet.

[Bo_Ry]

Thanks, Glenn

Congratulations on your promotion.

[t0mrogers]

Sorry SUM of numbers in A29 back sheet need to pull through a a SUM on D3 front sheet.

[Bo_Ry]

Please try at D3 and press Ctrl+Shift+Enter

=SUM(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE('Back (Feed) Sheet'!A29,CHAR(10)," ")," ",REPT(" ",100)),ROW(INDIRECT("1:20"))*100-99,100),))


or this press Ctrl+Shift+Enter and drag down

Please Login or Register  to view this content.

[t0mrogers]

Please try at D3 and press Ctrl+Shift+Enter

=SUM(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE('Back (Feed) Sheet'!A29,CHAR(10)," ")," ",REPT(" ",100)),ROW(INDIRECT("1:20"))*100-99,100),))


or this press Ctrl+Shift+Enter and drag down

Please Login or Register  to view this content.

Hi Bo,

When I try Ctrl,Shift,Enter it just opens the Save file window?

[Bo_Ry]

Please download sheet from post#17 and see.

[t0mrogers]

Thank Bo, you are a star

[t0mrogers]

Please download sheet from post#17 and see.

Hi Bo

Can I just ask, if I amend the report I am using so the cell only contains numbers and no text - Will the formula you provided still work?

Regards
Tom

[Bo_Ry]

It should work if the delimiters of each number are space or char(10) (Alt+Enter).

[t0mrogers]

Try this:

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


=SUMnUMBERS(A1)

Hi

I tried this method and it came back with 789, one of the values is 788 and the other is 1269 - Any ideas?