Use LEFT in sum_range parameter of SUMIF function (Excel)

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.