Sum one column if a word is found in EITHER of multiple other columns

[kmanbob]

Column A contains numbers that I want to SUM. But only if they correspond to cells from EITHER of any other columns that contain a word.

For instance

A1 = 100
A2 = 50
A3 = 25
A100 = 5

B1 = max
B2 = (empty)
B3 = (empty)
B100 = (empty)

C1 = (empty)
C2 = maxwell
C3 = (empty)
C100 = the max

How do I make a formula so that IF EITHER columns B OR C contain "max" in any way, that the corresponding cells in column A will be summed ? And just incase, what if I wanted to include multiple other, column D for instance.

In this case the result that I want would be the sum of A1,A2,A100 which would be 155

[Tony Valko]

Try this...

=SUMPRODUCT((ISNUMBER(SEARCH("max",B2:C100)))*A2:A100)

[kmanbob]

Try this...

=SUMPRODUCT((ISNUMBER(SEARCH("max",B2:C100)))*A2:A100)

Thank you for replying...

this sort of works, but the major problem with this is...

If both cells B2 and C2 contain the word max, the formula with add A2 twice! I need a formula that will understand that if either or both cells in column B & C contain the word, "max" in this example, that A2 will be summed as only one instance.

TIA


UPDATE ... here is the answer and it works as I had intended...

Excel 2010
A B C D E
1 100 max 155
2 50 maxwell
3 25
4 5 the max
Sheet1


Array Formulas
Cell Formula
E1 {=SUM(IF(NOT(ISERROR(FIND("max",B1:B4))),A1:A4,IF(NOT(ISERROR(FIND("max",C1:C4))),A1:A4)))}

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

[Tony Valko]

Here's the SUMPRODUCT version:

=SUMPRODUCT(SIGN(ISNUMBER(SEARCH("max",B1:B4))+ISNUMBER(SEARCH("max",C1:C4))),A1:A4)