# Use LEFT in sum_range parameter of SUMIF function (Excel)

I have a lot of columns on top of each other, like so:

I would like to find all of the cells in this range that begin with `1a`, and then sum up whatever follows. So in this example, we find `1a10` and `1a20`, so our result would be 10+20, or `30`.

The difficult thing is that after the `a`, there could be one or two digits, and I would also like to expand it to work with two-digit numbers before the `a` as well. So we could have:

```1a10 1a1 11a1 11a10```

I've tried experimenting with what I've learnt about `SUMIF`, `SUMIFS` and `SEARCH`, but while I have been able to (to some extent) detect which cells to select, cutting off the start of it, especially when the length of the end and the start can vary, left me with a jumble of stacked `IF` statements and an nonfunctional formula.

Unfortunately, I cannot use macros and my school is only up to Excel 2013, but I hope this is still possible.

Thanks.

• Your question is really clear. " I would also like to expand it to work with two-digit numbers before the a " so do you need to look for starting with 1a or containing 1a? – Máté Juhász Oct 4 '18 at 4:27

You can do this with an array formula

``````=SUM((LEFT(\$A\$2:\$A\$8,2)="1a")*(IF(ISNUMBER(--RIGHT(\$A\$2:\$A\$8,LEN(\$A\$2:\$A\$8)-2)),RIGHT(\$A\$2:\$A\$8,LEN(\$A\$2:\$A\$8)-2),0)))
``````

Because this is an array formula you need to use Control+Shift+Enter instead of just Enter.

Additionally since its an array formula, you want to avoid full column references such as `A:A`

You will know you have entered it correctly when you see { } around your formula. Note the { } may not be added manually.

### UPDATE

If you want to make the formula a bit more robust to deal with longer search strings such as 22a, and allow you to use a cell for the ID string you want to sum by, you can use the following formula.

``````=SUM((LEFT(\$A\$1:\$A\$8,LEN(TRIM(C2)))=C2)*(IF(ISNUMBER(--RIGHT(\$A\$1:\$A\$8,LEN(\$A\$1:\$A\$8)-LEN(C2))),RIGHT(\$A\$1:\$A\$8,LEN(\$A\$1:\$A\$8)-LEN(C2)),0)))
``````

Note this is still an array formula. Also the range has been increase to include the data value of a single digit after your key that you are searching for.

minor update, changed the `-RIGHT` to `--RIGHT`...but apparently the -- may not even be needed.

• It works perfectly; exactly what I needed, and works flawlessly with any length of ID or number! I'm surprised how you were able to make the formula so compact, as well, especially when dealing with something so complicated. – Geza Kerecsenyi Oct 4 '18 at 19:17

I don't want to do your work for you, but I can give you some suggestions...

There are ways you could do this with one or two big, long formulas, but you can break it up into several formulas in separate columns to make it easier to understand. For example, in B1 you could use

`=IF(LEFT(A2,2)="1a",1,0))` This will return a 1 for your cells that start with "1a" and a 0 for those that don't.

In C1, you could use

`=LEN(A2)` This will return the number of characters in A2.

From there, start writing IF statements with formulas (pseudo code below, not actual formulas).

``````"If A2=1 AND C1=4, return RIGHT(A2,2)"

"If A2=1 AND C1=3, return RIGHT(A2,1)"
``````

All I'm doing is setting up the sheet to get the "10" from 1a10 into its own column. You can then do whatever algebra you want on the resulting numbers.

• And how would I sum them up? I would need a 3-dimensional structure as I have 20 different numbers to go before the `a`, which would be a very complicated helper column structure... – Geza Kerecsenyi Oct 3 '18 at 20:09
• "I don't want to do your work for you, but I can give you some suggestions" this type of hinting doesn't fit well in this site. If you indeed don't want to provide a solution then write only a comment. If you write an answer then write it well, now you spent more time on hinting than writing the solution. Writing a few formula is not "doing others' job" – Máté Juhász Oct 4 '18 at 4:10
• @ Máté Juhász - Thanks for the tips, I'm still new to the site. Did not have enough reputation to post comments when I put my original post. My approach to the OP was essentially to do each of the required calculations/formulas as a separate column to make the logic easier to follow than the single-line array formulas above. – user2800 Oct 4 '18 at 12:04
• @Geza Kerecsenyi Given your comment above, then yes, the other answers will work much better than mine. – user2800 Oct 4 '18 at 12:06

Here is another approach with `SUBSTITUTE`:

`=SUM(IFERROR(SUBSTITUTE(A1:A7,C1,"")*1,0))`

How it works:

• `=SUM(IFERROR(SUBSTITUTE(A1:A7,C1,"")*1,0))`
• `SUBSTITUTE(A1:A7,C1,"")` - removes `1a` from all rows
• `SUBSTITUTE(...)*1` - converts entries to number where possible (only rows where `1a` was removed previously), others will result error
• `IFERROR(...,0)` - converts errors to `0`

Also this is an array formula, so need to press CTRL+SHIFT+ENTER after typing it.