I want to get the sum of the text inside (<number>) like the table below

tried this formula but this does not work or throws an error

{=SUMIF($D1:$D7, $F1&"(*", -RIGHT($D1:$D7, 3))}

RIGHT works okay with SUM like this formula

{=SUM(-RIGHT(D1:D7,3))}

CodePudding user response：

The SUMIF function takes the following parameters:

1. the range of cells to inspect
2. the criteria
3. the range of cells to sum

So the third parameter MUST be a range of cells.

But -RIGHT($D1:$D7, 3) doesn't return a range of cells - it returns an array of values.

That means SUMIF isn't the right tool. Instead, make your {=SUM(-RIGHT(D1:D7,3))} array formula conditional by multiplying each value by one or zero based on the values in F:

{=SUM( (LEFT(D$1:D$7,2)=F1) * (-RIGHT(D$1:D$7,3)) )}

This is an array formula so must be entered by using Control-Shift-Enter instead of just Enter alone. Put it in G1 and then copy it down to the other cells.