Help with SUMPRODUCT IF type of function

**JVAllStar** · 06-21-2012, 01:50 PM

I would like to use a formula that accomplishes the following:

If the values in the row A contain the word "Retail" , then multiply the value in column B with column C and add the results , if not, then 0.

I would expect the result to be 100 (from row 1) + 25 (from row 3) = 125

A B C
Retail store 10 10
Direct 10 10
Retail online 5 5

**NBVC** · 06-21-2012, 01:54 PM

you mean?

=SUMPRODUCT(--Isnumber(Search("Retail",A1:A100)),B1:B100,C1:C100)

**JVAllStar** · 06-21-2012, 02:05 PM

Yes! Perfect! Thank you!

Just so I "learn how to fish" instead of you just giving me a fish, what is the point of the --ISNUMBER being added to this formula? Maybe i should ask in 2 seperate questions: why do you add the isnumber, and why do you precede it with --?

**NBVC** · 06-21-2012, 03:01 PM

Firstly, we are searching for "Retail" in the cells, SEARCH() results in a numeric position if the string is found, and #VALUE! error if not found... so we need to find all the cells which SEARCH() resulted in numeric results (positions were determined). All we need to know is if a number was returned (we don't care what number).. so ISNUMBER() checks if SEARCH() returned a numeric result... ISNUMBER() then shows its results as TRUE (if numeric) and FALSE (if not). The -- then coerces the TRUEs and FALSEs to 1s and 0s, respectively, so we can carry on and do the SUMPRODUCT() math. ... now go catch lots of fish