Sumproduct combined with If(IsNumber(Search

0
votes

I have a column of values that I would like to calculate the sum product with a second column depending on what string is in the second column.

i.e. if the second column has the string "estimate" I would like to multiply the value in the first column by 0.6. if the second column has the string "calculated" I would like to multiply the value in the first column by 0.9 if the second column has the string "weighed" I would like to multiply the value in the first column by 0.97.

and then sum all of these values together.

I have made a start:

=SUMPRODUCT(J5:J27*(IF(ISNUMBER(SEARCH("Estimate",M5)),0.6,IF(ISNUMBER(SEARCH("Calculated",M5)),0.9,IF(ISNUMBER(SEARCH("Drawing/Weighed",M5)),0.97)))))/$J$29

However this only changes the sum product based on the contents of M5 and not each row in turn.

Cheers!!

1
excelsearchisnumeric

1 Answers

0
votes

Worked out a way round it:

=(SUMPRODUCT((M5:M27= "Estimate")*J5:J27)*0.6+SUMPRODUCT((M5:M27= "Cad")*J5:J27)*0.9+SUMPRODUCT((M5:M27= "WeighedDrawing")*J5:J27)*0.97)