+ Reply to Thread
Results 1 to 2 of 2

Summing Filtered Data with Numbers & Text

  1. #1
    Registered User
    Join Date
    Oklahoma City
    MS-Off Ver
    Excel 2010

    Summing Filtered Data with Numbers & Text

    I currently have an array formula which allows me to add the numbers of cells containing both numbers and text:

    =SUM(IFERROR(--LEFT(J4:J999;FIND("m";J4:J999)-1);0)) &"m - "&SUM(IFERROR(-SUBSTITUTE(MID(J4:J999;FIND("-";J4:J999);99);"b";"");0))&"b"

    For example, if J4:J8 were:

    10m - 2b
    3m - 12b
    0m - 1b
    1m - 0b
    11m - 11b

    the formula would result in "25m - 26b"

    I, however, would like a formula which would only apply to cells after I've filtered based on criteria in another column.

    I know it's a bit complicated but any help would be appreciated.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365

    Re: Summing Filtered Data with Numbers & Text

    Excel provides so many columns, for no extra charge, that you can use to take "weird" data and turn it into "not weird" data, and after you've done that, ALL the normal easy to use non-crazy functions are yours to use with no fuss, no muss.

    10m - 2b

    You obviously consider that TWO pieces of information, don't you? Having it iin one cell is quaint, but look at what you're doing to yourself trying to "do math" on this? Let's fix the data so everything is easy for you and for others who try to use or understand your data and/or formulas.

    So, assuming you must keep this weird data in column F, let's use two other "free" columns to create an "m" and an "b" data set.

    L4: "m"
    M4: "b"
    L5: =SUBSTITUTE(LEFT(J4,FIND(" ",J4)),"m","")+0
    M5: =SUBSTITUTE(RIGHT(SUBSTITUTE(J4, " ", REPT(" ", 20)),20),"b","")+0

    Now copy L5 and M5 down (obviously you can use any free columns, this is just an example) as far as you want, it's harmless to go an extra couple 100 rows to allow for expansion.

    Now that you have a couple of normalized data columns, your original formula can be switched to simple SUBTOTAL functions (SUBTOTAL ignores hidden rows) and you get:

    =SUBTOTAL(9,L:L)&"m - " & SUBTOTAL(9,M:M)&"b"
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Summing Numbers contingent on text
    By Janie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-10-2014, 11:34 AM
  2. Summing numbers together from one column that share the same text value
    By greatanaugi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-21-2013, 10:24 AM
  3. Replies: 4
    Last Post: 09-04-2012, 06:09 AM
  4. summing cells with text and numbers
    By val in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-01-2006, 10:30 PM
  5. Summing cells that contain numbers and text
    By Mango in forum Excel General
    Replies: 10
    Last Post: 05-13-2006, 01:25 PM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1