How to sum one column only if corresponding cells from EITHER of any other column meets criteria , contains a word

[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 desired result would be the sum of A1,A2,A100 which would be 155

 

[Sal Paradise]

I'm sure someone can do this prettier, but this works:

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

Cell Formulas
Range	Formula
E1	{=SUM(IF(NOT(ISERROR(FIND("max",B1:B4))),A1:A4,IF(NOT(ISERROR(FIND("max",C1:C4))),A1:A4)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kmanbob]

THANK YOU

This works perfectly.

Which is if either column B or C or both contain "max" that the corresponding cell in A will be summed as one instance! (I was messing with a SUMPRODUCT formula that would sum the corresponding cell in A twice if both B & C contained "max" ... =SUMPRODUCT((ISNUMBER(SEARCH("max",B2:C100)))*A2:A100))

THANK YOU

I'm sure someone can do this prettier, but this works:

Excel 2010

	A	B	C	D	E
max
maxwell
the max

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]155[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]{=SUM(IF(NOT(ISERROR(FIND("max",B1:B4))),A1:A4,IF(NOT(ISERROR(FIND("max",C1:C4))),A1:A4)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(SEARCH("max",B1:B100&"|"&C1:C100)),A1:A100))

 

[Sal Paradise]

That is even easier:

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

Cell Formulas
Range	Formula
E1	{=SUM((NOT(ISERROR(FIND("max",$B$1:$B$4)))+NOT(ISERROR(FIND("max",$C$1:$C$4))))*$A$1:$A$4)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sal Paradise]

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(SEARCH("max",B1:B100&"|"&C1:C100)),A1:A100))

Man I love your formulas. So elegant.

 

[kmanbob]

Thank you, indeed this array formula (SHIFT+CTRL+ENTER) is quite superb and succinct...

but is it just me or there seems to be a little lag in calculation when using an array formula, I am on Excel 2011 for Mac... there is almost a 1 second lag when I input data

Also, I am still very much an amateur when it comes to Excel, so I haven't grasped the difference between an array formula and just a regular formula that contains a range...

in my example, say, a regular formula (just pressing ENTER) with the range being Columns B:C

Thank you for the formula

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(SEARCH("max",B1:B100&"|"&C1:C100)),A1:A100))

 

[Aladin Akyurek]

Man I love your formulas. So elegant.

Thanks Sal.

Thank you, indeed this array formula (SHIFT+CTRL+ENTER) is quite superb and succinct...

You are welcome.

but is it just me or there seems to be a little lag in calculation when using an array formula, I am on Excel 2011 for Mac... there is almost a 1 second lag when I input data

Such a lag with 100 rows of data is somewhat strange. You have perhaps many other formulas in your workbook.

The following set up would be faster:

D1, just enter and copy down:

=B1&"|"&C1

Now we can invoke a faster, range-processing formula:

=SUMIF(D1:D100,"*max*",A1:A100)

Also, I am still very much an amateur when it comes to Excel, so I haven't grasped the difference between an array formula and just a regular formula that contains a range...

in my example, say, a regular formula (just pressing ENTER) with the range being Columns B:C

Thank you for the formula

There is enough on array formulas on the internet, but you could start with Help of Excel.

 

[kmanbob]

Thank you for being so direct

If you could help me with my other predicament...

http://www.mrexcel.com/forum/excel-...ther-column-s-contain-word-s.html#post3469973

I am trying a slightly different approach to summing by criteria from another column, but felt it should get a different thread, I'd appreciate it if you could stop by...

THANKS

Thanks Sal.



You are welcome.



Such a lag with 100 rows of data is somewhat strange. You have perhaps many other formulas in your workbook.

The following set up would be faster:

D1, just enter and copy down:

=B1&"|"&C1

Now we can invoke a faster, range-processing formula:

=SUMIF(D1:D100,"*max*",A1:A100)



There is enough on array formulas on the internet, but you could start with Help of Excel.