SumProduct with Left functions

[Robert Christie]

Hi
I'm trying to add dollar values in column E if the first
word in a sentence in column F equals CAROL
I have tried to couple sumproduct and left functions
together without success.

Thanking you in advance
Bob

 

[Aladin Akyurek]

=SUMPRODUCT(--(LEFT($A$2:$A$4,SEARCH(" ",$A$2:$A$4)-1)=D1),$B$2:$B$4)

where D1 houses a value like Carol, A2:A4 sentences, and B2:B4 dollar
values.

 

[Robert Christie]

Thankyou Aladin
Works great, I changed the D1 reference to "CAROL" and
that worked as well.
Thankyou again.

Bob C

 

[Don Guillett]

try this version
=sumproduct((left(a2:a22,5)="CAROL")*e2:e22)

 

[Tom Ogilvy]

What about Carolyn? I believe that is why Aladin was using Find to locate a
space.

 

[Tom Ogilvy]

of course you could do

=sumproduct((left(a2:a22,6)="CAROL ")*e2:e22)

 

[Don Guillett]

But he said, "the first WORD" and left,5 would also get carolyn or carol
lynn

 

[Robert Christie]

Thanks Don
It never fails to amaze me how many different ways there
are in Excel to do the same thing.
Thankyou

Bob C

 

[Robert Christie]

Thanks Tom
For your reply, sorry my thankyou is late had to have a
sleep first to read it Sunday morning here in Australia

Have a good weekend

Bob C

 

[Tom Ogilvy]

Yes it would and Carolyn would be incorrect, Yes? (Carol Lynn has a first
word of Carol - so meets the criteria).

 

[Don Guillett]

Tom,
CAROL ss 2 =SUMPRODUCT((LEFT(K1:K5,5)="Carol")*L1:L5)
carol ffff 2 7
carol 1
carolyn 2

 

[Tom Ogilvy]

Yes, returns 7 and the correct answer is 5. Row 3 is not a sentance, so
really isn't a proper test instrument, but row 4 definitely doesn't start
with the word Carol.

Regards,
Tom Ogilvy