CODEWITHSUNDEEP
excelexcel-formula
Mohammad
Mohammad

Reputation: 3547

how to specify cell value according to its content in sum formula

I have a column contains some texts as follow:

one
two
three
four

I want to sum the values this column cells according to their content, so I should check the content then return a value, as if(cell = one) then 1

so the sum result should be 1+2+3+4 = 10 I tried to do a formula like =SUM(IF(A1=apartment,1),...) but its absolutely wrong.

how can I write this formula?

Upvotes: 1

Views: 44

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

You can also do:-

=SUMPRODUCT((A1:A10={"One","two","three","four"})*{1,2,3,4})

This builds up a 2d array where the rows correspond to your data and the columns correspond to the strings "one","two","three" and "four". The elements are set 'true' only where the data matches one of the four strings. Then this array is multiplied by the row of numbers 1,2,3 and 4. 'TRUE' counts as 1 in the multiplication and 'FALSE' counts as 0.

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

Count the words and multiple by the associated values:

=COUNTIF(A:A,"one")+2*COUNTIF(A:A,"two")+3*COUNTIF(A:A,"three")+4*COUNTIF(A:A,"four")+5*COUNTIF(A:A,"five")

enter image description here

You can extend this formula by adding more terms if necessary, or use a VLOOKUP() table.

Upvotes: 1

Related Questions