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.
Bookmarks