How to sum one column only corresponding cells from EITHER of other columns meet criteria, contain a word

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 columns, column B,C,D for instance.

In this case the desired result would be the sum of A1,A2,A100 which would be 155

Excel Version Dependent

If you have excel 2007+
Use SUMIFS:
=SUMIFS(A1:A100,B1:B100,"MAX",C1:C100,"MAX")

If pre 2007 (2003 or lower)
Use SUMPRODUCT:
=SUMPRODUCT(A1:A100,N(B1:B100="MAX"),N(C1:C100="MAX"))

***edit***
Oops just noticed the EITHER.
The above would be AND
***edit***

Nick's picture

add another column (D)

add another column (D) containing the logic:
=OR(B1="max",C1="max")

... then use sumif

need to find mac for helper column

@Nick, the OR will not work for cells containing "max", just those equal to "max".

For the helper column try:

=IF(ISERROR(FIND("MAX",UPPER((B3&C3)))),0,1)

without helper:

=SUMPRODUCT(A3:A9,IF(ISERROR(FIND("MAX",UPPER((B3:B9&C3:C9)))),0,1))
(enter using [ctrl]+[shift]+[enter])