How to create a formula that multiplies a total by the number contained in a dropdown?

Question

I'm writing a high-level rate card, and I've got a list of line items along with costs for testing, and deployments with various ranges (testing high 25%, testing medium 50%, testing low 75%) which I have in a dropdown already. I'm after a formula that I can use to sum the total hours I've allocated and then multiply by 0.25, 0.5, 0.75, respectively, based on what option is selected in the dropdown.

I have this gnarly formula which works, but I'm wondering if there is a better, cleaner way to do this.

=IF(ISNUMBER(SEARCH("75%",A19)),SUM(B5:B18)*0.75,IF(ISNUMBER((SEARCH("50%",A19))),SUM(B5:B18)*0.5,IF(ISNUMBER((SEARCH("25%",A19))),SUM(B5:B18)*0.25)))

Answer 1

You can use this formula:

=SUM(B5:B18)*AGGREGATE(14,7,{0.25,0.5,0.75}/ISNUMBER(SEARCH({"25%","50%","75%"},A19)),1)

or

=SUM(B5:B18)*SUMPRODUCT(ISNUMBER(SEARCH({"25%","50%","75%"},A19))*{0.25,0.5,0.75})

or this array formula:

=SUM(B5:B18)*MAX(IF(ISNUMBER(SEARCH({"25%","50%","75%"},A19)),{0.25,0.5,0.75},0))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.