# Summing Filtered Data with Numbers & Text

1. ## 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. ## 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"

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

#### 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