Microsoft Office Forums Extract numbers from parentheses and add them up

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-23-2019, 08:35 AM
pokeyarw pokeyarw is offline Extract numbers from parentheses and add them up Mac OS X Extract numbers from parentheses and add them up Office 2011 for Mac
Novice
Extract numbers from parentheses and add them up
 
Join Date: Apr 2019
Posts: 5
pokeyarw is on a distinguished road
Default Extract numbers from parentheses and add them up

Greetings,
I’d like to extract some parenthesized numbers from a column of cells. E.g.
6031, Cash(40)
Cash(44)
2722

Cash
5887
1985
1670
1246, 1245
1398
I can extract the amount of cash with

=VALUE(IFERROR(MID(Q5,SEARCH("(",Q5,1)+1,SEARCH(") ",Q5,1)-SEARCH("(",Q5,1)-1),0))


which works just fine, but I don’t really want the individual values, I want the sum. If I copy fill this down it produces all the right numbers. There’s probably a more elegant way to do this, but this works.
But I really want the total and it seems like a sumproduct or just a sum should give me the sum but it gives me zero, e.g.,

=SUMPRODUCT((IFERROR(VALUE(MID(Q5:Q33,SEARCH("(",Q 5:Q33,1)+1,SEARCH(")",Q5:Q33,1)-SEARCH("(",Q5:Q33,1)-1)),0)))

I’m obviously working in column Q and the array I want to sum is Q5:Q33.
What am I missing?

For reasons I don't understand, the published version of this post shows a space between the Q and the 5 in the first SEARCH looking for the "(". It's not there in my usage.
Reply With Quote
  #2  
Old 04-23-2019, 11:00 AM
xor xor is offline Extract numbers from parentheses and add them up Windows 10 Extract numbers from parentheses and add them up Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Your SUMPRODUCT formula works fine for me.
Reply With Quote
  #3  
Old 04-23-2019, 02:40 PM
pokeyarw pokeyarw is offline Extract numbers from parentheses and add them up Mac OS X Extract numbers from parentheses and add them up Office 2011 for Mac
Novice
Extract numbers from parentheses and add them up
 
Join Date: Apr 2019
Posts: 5
pokeyarw is on a distinguished road
Default Extract numbers from parentheses and add them up

Well, that's the pits! I copied the formula directly fro the spreadsheet and it gives me zero. I did
=SUMPRODUCT(Q74:Q91)
Q74:Q91 is where I did the fill down to see that the extract was working properly. The SUMPRODUCT above gave me 84, which is correct.

The items to be summed are all entered from the keyboard, so they are not computed. I've got Office 2011 on my Mac. Is that the issue?
Reply With Quote
  #4  
Old 04-23-2019, 09:54 PM
xor xor is offline Extract numbers from parentheses and add them up Windows 10 Extract numbers from parentheses and add them up Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

I am not sure what the problem is.
Reply With Quote
  #5  
Old 04-29-2019, 06:01 AM
pokeyarw pokeyarw is offline Extract numbers from parentheses and add them up Mac OS X Extract numbers from parentheses and add them up Office 2011 for Mac
Novice
Extract numbers from parentheses and add them up
 
Join Date: Apr 2019
Posts: 5
pokeyarw is on a distinguished road
Default followup on extract numbers from a range and add them up

Greetings,
As indicated in the previous messages, SUMPRODUCT for Office 2011 (Mac) seems not to be bulletproof. I have attached a demonstration file. It works fine in google/sheets. All three highlighted cells have the same correct computed values. I tried it in Numbers (Mac) and cell E5 came out #VALUE, but the other two highlighted cells were correct. Considering some of the constructs I've seen using SUMPRODUCT this application seems pretty pedestrian. I don't want to use a "helper" column as I have to do this every week. Any ideas?? I post this mostly to inform others of the problem here, but I'd like to be able to get the result without creating lots of useless intermediate cells.

Column A contains a list of check numbers, sometimes with a parenthesized number. The latter is cash. I want to add the cash.
Column C is the formula which extracts the cash from the corresponding cell in Column A.
Cell D5 is the SUMPRODUCT of the array represented by Column C.
Cell C24 is the correct answer achieved by summing the column above.
Cell E5 is the SUMPRODUCT of the numbers in Column A times the 1ís in Column E just to see if the problem is that two arrays are required for SUMPRODUCT to work properly. That it gives an error is curious as the only error condition for SUMPRODUCT, given that there are no errors in Column C, seems to be that the two arrays are not the same size & shape. But they are the same size & shape.
Reply With Quote
  #6  
Old 04-29-2019, 06:40 AM
xor xor is offline Extract numbers from parentheses and add them up Windows 10 Extract numbers from parentheses and add them up Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

I don't see any file.
Reply With Quote
  #7  
Old 04-29-2019, 05:06 PM
pokeyarw pokeyarw is offline Extract numbers from parentheses and add them up Mac OS X Extract numbers from parentheses and add them up Office 2011 for Mac
Novice
Extract numbers from parentheses and add them up
 
Join Date: Apr 2019
Posts: 5
pokeyarw is on a distinguished road
Default Extract numbers from parentheses and add them up

Here you go, I think.
Attached Files
File Type: xlsx foo.xlsx (34.8 KB, 1 views)
Reply With Quote
  #8  
Old 04-29-2019, 09:29 PM
xor xor is offline Extract numbers from parentheses and add them up Windows 10 Extract numbers from parentheses and add them up Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Same remark as I gave in #2.
Attached Files
File Type: xlsx foo_2.xlsx (10.8 KB, 3 views)
Reply With Quote
  #9  
Old 05-06-2019, 01:04 PM
pokeyarw pokeyarw is offline Extract numbers from parentheses and add them up Mac OS X Extract numbers from parentheses and add them up Office 2011 for Mac
Novice
Extract numbers from parentheses and add them up
 
Join Date: Apr 2019
Posts: 5
pokeyarw is on a distinguished road
Default Extract numbers from parentheses and add them up

I checked it out with SUM and it gives the same result (40). That seems odd. It is also odd that it works in Sheets. SUMPRODUCT seems more complex than SUM and it not working for either (on a Mac as I assume you are Windows) implies something pretty basic is not working as expected.
Reply With Quote
  #10  
Old 05-06-2019, 09:50 PM
xor xor is offline Extract numbers from parentheses and add them up Windows 10 Extract numbers from parentheses and add them up Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,029
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

I tried it out in Sheets and it works fine for me, both SUMPRODUCT and SUM. With the SUM-function you have to array-enter (hold down Ctrl and Shift before pressing Enter).
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract numbers from parentheses and add them up Allignment of numbers with parentheses RussBell Mail Merge 2 10-28-2016 05:51 AM
Extract numbers from parentheses and add them up Is there a quick way to remove the dashes and parentheses from phone numbers? Stacy Excel 2 09-12-2014 07:03 AM
How to extract only numbers from a STRING? Learner7 Excel 3 07-02-2013 06:25 AM
Extract Numbers from Zip Code Karen615 Excel 3 09-21-2011 06:54 AM
Extract numbers from a text string aleale97 Excel 4 02-10-2011 10:33 AM


All times are GMT -7. The time now is 04:50 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft