CODEWITHSUNDEEP
excelexcel-formula
yinka
yinka

Reputation: 134

Modify Column / Range in Excel

For a cell B1 with value 063, I can use B1&_'txt' in a formula like this:

sumif(A:A, B1&"_txt", Z:Z)

Is there a way of do something similar to a column? like:

sumif(A:A&"_txt", B1, Z:Z)

Which in this case appends _txt to all values without need for a helper column

Upvotes: 0

Views: 78

Answers (2)

chancea
chancea

Reputation: 5968

You can use an array formula:

Note array formulas need to be entered using Ctrl + Sift + Enter

=SUM(IF(A:A&"_text"=B1,Z:Z,0))

Upvotes: 1

cybernetic.nomad
cybernetic.nomad

Reputation: 6408

Rather than always adding the suffix "_txt" how about removing it from B1:

=SUMIF(A:A, LEFT(B1,LEN(B1)-4),Z:Z)

Upvotes: 0

Related Questions