CODEWITHSUNDEEP
excelexcel-formula
macka001
macka001

Reputation: 11

Formula to check cells and then perform sum action

In excel, i need to check the text value of 3 cells, A1, A10 & A20 if either contains a text string CK75 then i need to add together values in cells B1, B10 & B20 and put the answer in Cell B2.

I started with an OR statement but i can only check 2 cells

=OR(B25="CK75 -Al",B27="CK75- Al")

=OR(B25="CK75 -Al",B27="CK75- Al")

I also tried this which works in part

=IF(ISNUMBER(SEARCH("CK75 - ",A20)),SUM(B1+B20),B1)

Upvotes: 1

Views: 56

Answers (2)

JvdV
JvdV

Reputation: 75900

From the first line of your question (and your second attempt) it appears as though the text string in these cells can contain the substring, rather than exactly be the searchvalue CK75. If that is the case you could do as follow:

enter image description here

The formula I used translates to:

=IF(ISNUMBER(FIND("CK75",A1&A10&A20,1)),SUM(B10,B1,B20),"")

FIND() will trigger on capital/non-capital. If you dont want that to happen you should replace that with SEARCH()

Upvotes: 2

Lucas
Lucas

Reputation: 675

This will do it and return blank if the condition is not met:

=IF(OR(A1="CK75",A10="CK75",A20="CK75"),SUM(B1,B10,B20),"")

Upvotes: 4

Related Questions