SumProduct with Left functions

  • Thread starter Thread starter Robert Christie
  • Start date Start date
R

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
 
=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.
 
Thankyou Aladin
Works great, I changed the D1 reference to "CAROL" and
that worked as well.
Thankyou again.

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

Bob C
 
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
 
Yes it would and Carolyn would be incorrect, Yes? (Carol Lynn has a first
word of Carol - so meets the criteria).
 
Tom,
CAROL ss 2 =SUMPRODUCT((LEFT(K1:K5,5)="Carol")*L1:L5)
carol ffff 2 7
carol 1
carolyn 2
 
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
 
Back
Top