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***
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])