Formula to search a string in a column and then multiply 2 columns

**Wizz** · 06-10-2010, 08:29 AM

Hi,
I need a formula that will check for a given string (Person's UserName) in a column (Range("A3:A203") and multiply the value in column "B" and "C" for each row where it finds the UserName.

I've tried some Ctrl+Alt+ENTER formulas, VLookUp's, LookUps without success. Problem is the range with the names is NOT sorted !

Any help is very appreciated.
Thanks again.

**DonkeyOte** · 06-10-2010, 08:42 AM

Do you perhaps mean

=SUMPRODUCT(--(A3:A203=A1),B3:B203,C3:C203)

where A1 holds username

(modify delimiters to suit your locale)

**canapone** · 06-10-2010, 08:43 AM

Hi, maybe

=SUMPRODUCT(--($A$3:$A$203=A3),$B$3:$B$203,$C$3:$C$203)

Regards

Edit: sorry, I did not mean to overlap: same answer.

**Wizz** · 06-10-2010, 09:31 AM

Thanks Donkeyote; Grazie Canapone!
It worked BEAUTIFUL !

But what does the "--" means? I've already read the Help file 3 times and find no clue !!

**DonkeyOte** · 06-10-2010, 09:33 AM

For a good overview of SUMPRODUCT and coercion see the link in my sig. to Bob Philips' white paper.

SUMPRODUCT has evolved beyond MS intentions hence the help file bears little relation to how the majority of us use it.