Countif, Match and sum?

[uknusa]

Looking for a formula please.

I have in Col D pairs of names in each cell and in Col E some numbers in each cell. I need to create a NEW column counting the amount of times a partial name appears in Col D and then add the number from Col E associated with it minus 1.

example: Count how many times "Bob" appears in Col D then on each row in Col D that has a "Bob" total the numbers in Col E then subtract 1

So the answer to the example above should read 10. Bob=2(from D) +5+3(from E)-1

COL D COL E
Bob Champ & Julie Foxx 5
Vic Black & Geoff Goode 2
John White & Bob Champ 3

[avk]

Kindly attach sample file with desired data.

[Fluff13]

With your example it should return 9 (2+5+3-1)
=COUNTIF(D:D,"*Bob*")+SUMIF(D:D,"*Bob*",E:E)-1

[uknusa]

Thanks for your help Fluff13. I had to tweak your formula slightly to finally get it. Sorry about the confusion.

=COUNTIF(D:D,"*Bob*")+SUMIF(D:D,"*Bob*",E:E)-countif(D:D,"*Bob*")

Cheers!

[Fluff13]

Unless I'm missing something the two parts in blue are just cancelling each other out
COUNTIF(D:D,"*Bob*")+SUMIF(D:D,"*Bob*",E:E)-countif(D:D,"*Bob*")So you could write it like
=SUMIF(D:D,"*Bob*",E:E)