Formula to check cells and then perform sum action

1
votes

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)
2
excelexcel-formula

2 Answers

4
votes

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),"")
2
votes

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()