0

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

Cells on top of each other

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.

  • 1
    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
3

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.

POC

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.

POC2

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
0

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
0

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.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.