CODEWITHSUNDEEP
excel
Bartosz Górski
Bartosz Górski

Reputation: 1160

Sum numerical values returned by functions without creating new columns/rows for them

I have a following problem: let's assume that I have the following four rows in the column A:

Alice: $5
Bob: $12
Alice: $23
Bob: $4

What I want to do is sum $ values for Alice only. I came up with this function that extracts numerical values from a single row:

=if(isnumber(find("Alice", A1)),right(A1, (len(A1) - find("$",A1))),0)

This way, a row gives me a $ value if it's Alice row or 0 if it's not. It's done in a way that would easily allow it to be reused for Bob if I need to. The problem is that normally i'd just use the sum function for summing up all the data in a column (=sum(A1:A4)), but using it for a function in this way:

=sum((if(isnumber(find("Alice", A1)),right(A1, (len(A1) - find("$",A1))),0)):(if(isnumber(find("Alice", A4)),right(A4, (len(A4) - find("$",A4))),0)))

apparently doesn't work. How should I do it? There's one more requirement, though - I can't create any column for storing values returned by those functions (if I could, I'd just create four new rows in, say, B1:B4 and just sum them).

Upvotes: 0

Views: 42

Answers (1)

Canoeron
Canoeron

Reputation: 26

In the following example the dataset is the named range "TestNames"

SUMPRODUCT(IF(ISERROR(FIND("Alice",TestNames)),0,ROWS(TestNames)^0) * (RIGHT(TestNames,LEN(TestNames) - FIND("$",TestNames))))

This is a regular formula and does not need to be input with CSE

Upvotes: 1

Related Questions