Reputation: 14883

Excel - sum given a condition in a relative column

Imagine I have this (time)sheet:

Hours | Text
------+----------------------
3     | fixing PRA-345
4.5   | refactoring PRA-222
5     | PRA-345 and stuff

And I want to calculate how much cumulative time one has spent on a ticket with a given number.

In other words sum the hours based on the text in a neighbouring cell.

Can you do it without extra column? what I did was to make an extra column that returned either the number, if given text was present (via REGEXMATCH) or 0. And then I ran a SUM on that column. Having this solved without extra column would be nice ;)

Expected output

In my case if would be enough for a given string to find the total sum of hours. So if I cell(say it's D1) has the hardwired text, such as "PRA-345" I want the cell to the left(E1) to display the total hours(8 in this case)

Upvotes: 0

Answers (3)

EtienneBerg

Reputation: 1

An additional column will need to be created to save the "ticket code" of the problem and then the simple functions provided above will implement it perfectly.

Upvotes: 0

Krzysztof Dołęgowski

Reputation: 2660

Is this what you need?

=sum(filter(B5:B,regexmatch(C5:C,E5)))

Reference:

FILTER
SUM

Upvotes: 4

marikamitsos

Reputation: 10573

Instead you can try

=QUERY({A1:A11,ArrayFormula(REGEXEXTRACT(B1:B11,"PRA-\d+"))}, 
           "select Col2, sum(Col1) where Col1 is not null 
            group by Col2 label Col2 'Tickets', sum(Col1) 'Sums' ",1)

Functions used:

Upvotes: 2

Excel - sum given a condition in a relative column

Answers (3)

Reference:

Related Questions