CODEWITHSUNDEEP
regexgoogle-sheetsarray-formulas
MattRogowski
MattRogowski

Reputation: 866

Range sum using regex

I have a spreadsheet with data as follows:

    |A    |B       |C       |D       |
+---+-----+--------+--------+--------+
|1  |Day  |Result 1|Result 2|Result 3|
+---+-----+--------+--------+--------+
|2  |Feb 1|A10-2   |B10-5   |B10-9   |
+---+-----+--------+--------+--------+
|3  |Feb 2|A10-9   |A10-9   |        |
+---+-----+--------+--------+--------+
|4  |Feb 3|B10-8   |A10-2   |B10-7   |

I want to get the sum of the second digit in each cell, in two totals based on the first letter. In other words, I want a sum of the second digit where the first letter is A, and a sum of the second digit where the first letter is B.

So, for A I am expecting to get 22 (2+9+9+2), and for B I am expecting to get 29 (5+9+8+7).

I have used REGEXEXTRACT to get the second digit, for example: 

=REGEXEXTRACT(B2,"A10-(\d+)")

this gives me 2. However, if I try: 

=REGEXEXTRACT(B2:D4,"A10-(\d+)")

to check all cells, I get an array value could not be found and I'm not sure what else to try.

Pseudocode for what I want would be something like: 

=SUM(REGEXEXTRACT(B2:D4,"A10-(\d+)"))

but I'm not sure if this is possible.

Upvotes: 1

Views: 464

Answers (1)

pnuts
pnuts

Reputation: 59460

Put A and B somewhere (say in E2 and E3) then enter and copy down: 

=sum(ArrayFormula(value(right(B$2:D$4))*--ArrayFormula(left(B$2:D$4)=E2)))

Presumably could be simplified but requirement seems to be too specialised to be worth much bother.

Upvotes: 2

Related Questions