If A1, A2, A3 = Hello then add B1, B2, B3

Hello all,

Is there a way to check all text values in Column A and if they are equal "Hello" or "Hello1" or "Hello2" then add all values in Column B and report it to Column c?

for example
A1, A2, A4 report "Hello", "Hello1", "Hello2". Now what I need is for excel to recognize the word "Hello" in all cells in Column A, then look at the value in column B, add them together then report it to a cell in column C. Below is a better example

- |----A----|----B---|---C
1 |-Hello--|---15--|-43
2 |-Hello1-|---23--|
3 |--Bye---|----1---|
4 |-Hello2-|----5---|
5 |--Bye1--|----8---|

Thank you for any help

Hi ADSK,

Try this:

Please Login or Register  to view this content.

Cheers,

Try this .......

=SUMPRODUCT(--(($A$1:$A$6)="hello")+--(($A$1:$A$6)="hello1")+--(($A$1:$A$6)="hello2"),(B1:B6))

Use this formula:

{=SUM(IF(LEFT(A1:A5,5)="Hello",B1:B5,0))}


Please note the curly brackets '{ }' are not put manually. These are Array functions. Here are the steps to input Array Functions:

• Select the cell where you want to put the formula
• Type out the formula without the curly brackets, and when you are done, do not press the 'Return' key (the 'Enter' key)
• Instead of hitting Return key, hold down the CTRL and SHIFT keys together and then press Return. So, the key combinations would be CTRL+SHIFT+RETURN, in place of RETURN key

I see no need for either SUMPRODUCT or SUM CSE here... a SUMIF should suffice surely ?

C1: =SUMIF(A1:A5,"Hello*",B1:B5)

Originally Posted by Stormseed

The + sign will coerce to numeric expressions hence there is no need to put the double unary (--) when you incorporate an OR condition in an array formula.

Moreover, your formula will not work accurately.
Assuming that you have the data in range A1:B7, try this:

Confirm formula with Ctrl+Shift+Enter:
=SUM(IF(ISNUMBER(SEARCH("*Hello*",A1:A7)),B1:B7))

Stormseed, I agree with you re: coercion but can you explain why you think mubashir's formula won't work ? I think it will work though I think mubashir would concede theat ConneXionLost has already shown a slightly better approach, ie:

=SUMPRODUCT(--(LEFT(A1:A5,5)="Hello"),B1:B5)

A Sumproduct would (IMO) generally be preferable to a CSE Array if only in terms of robustness for end user (where viable).

Stormseed, the OP does not imply embedded strings.

Moreover were that the case there's nothing to infer that:

asasHello-asas

should be treated as an instance of Hello

Originally Posted by DonkeyOte

Stormseed, the OP does not imply embedded strings.
Moreover were that the case there's nothing to infer that:
asasHello-asas
should be treated as an instance of Hello

I couldn’t read the post completely and just guessed …. But I’ve just read it again and come upt with below solution
but only ADSK can tell exactly that actually what he required

We can have “–“ or might be there is no “–“ before hello ….. all above formula will not work well
especially when we have “michello” or “hellodean” …. So we should only summing the hello where it comes
with hello1 or Hello2 or –hello - - etc…

So I’ve created below Array formula. I know there can be many possibilities but I created formula only by
keeping above possibilities ….. here we go

Don't forget to Press Ctrl+shift+Enter (CSE)

=SUM(IF((IF(ISERROR(IF(MID(A1:A5,SEARCH("-"&"Hello","-"&A1:A5),5)="hello",1,0)),0,1)*
(ISNUMBER(0+SUBSTITUTE(MID(A1:A5,SEARCH("-"&"Hello","-"&A1:A5)+5,1),"-","1"))))+(IF(ISERROR(SEARCH(" "&"hello"&" "," "&A1:A5&" ",1)),0,1)),1,0)*(B1:B5))

-MICHELLO FALSE
HELLO TRUE
-HelloD-- FALSE
--M--HELLO TRUE
--HELLO2- TRUE

I’ve made some new changes in Array formula and make it small while avoiding extra work …. Now it will fulfill all above conditions ……

CTRL+SHIFT+ENTER

=SUMPRODUCT(ISNUMBER(0+SUBSTITUTE(IF(LEN(MID(TRIM(A1:A5),SEARCH("-"&"Hello","-"&TRIM(A1:A5))+5,1))
>0,MID(TRIM(A1:A5),SEARCH("-"&"Hello","-"&TRIM(A1:A5))+5,1),"-"),"-","1"))*(B1:B5))

@ stormSeed did I say something to you ??? No regretting pls. I always welcome suggestion as you know when we do programming we made long long routines / procedures but in the end come up with a short solution ……..

@ DK I know you can come with a better solution but I need some clarification about in my above formula in my last post ....

In my above formula I tried 3 conditions and in third condition I’ve to work a lot to make 2 as 1 by using IF condition. Here is the details

={0,1,0,1,0}*{0,1,0,1,0}+{1,1,0,1,0}
={0,1,0,1,0}*{0,1,0,1,0}+{1,1,0,1,0}
={0,1,0,1,0}*{0,1,0,1,0}+{1,1,0,1,0}
={0,1,0,1,0}+{1,1,0,1,0}
Here I want to add (or take as OR) them
={1,2,0,2,0}
Now I've to convert 2 in 1 by using IF because then it will multiply with values else result will be different …….
=IF({1,2,0,2,0},1)
={1,1,0,1,0}
In Last step I multiply values
=SUM({1,1,0,1,0}*{1,2,3,3,2})
=SUM({1,2,0,3,0})
Result is 6