Count if match any of 3 strings

I'm using this formula and looking for a way to simplify it.

=SUM((COUNTIF(E5:E41,"DL380")*2),(COUNTIF(E5:E41,"DL580")*4), (COUNTIF(E5:E41,"RP4400")*4), (COUNTIF(E5:E41,"DL585")*4))

As more models get added it will become tougher to maintain. I'm trying to say count all the cells with "RP4400, DL580 or DL585" in them. The list will grow. Is there a way to say count if it matches "any value stored in this range"?

A couple of ways

=SUMPRODUCT((E5:E41={"DL380","DL580","RP4400","DL585"})*({2,4,4,4}))

or


=SUMPRODUCT(COUNTIF(E5:E41,{"DL380","DL580","RP4400","DL585"}),({2,4,4,4}))


--

Regards,

Peo Sjoblom

"GGG" <[email protected]> wrote in message
news:[email protected]...
>
> I'm using this formula and looking for a way to simplify it.
>
> =SUM((COUNTIF(E5:E41,"DL380")*2),(COUNTIF(E5:E41,"DL580")*4),
> (COUNTIF(E5:E41,"RP4400")*4), (COUNTIF(E5:E41,"DL585")*4))
>
> As more models get added it will become tougher to maintain. I'm
> trying to say count all the cells with "RP4400, DL580 or DL585" in
> them. The list will grow. Is there a way to say count if it matches
> "any value stored in this range"?
>
>
> --
> GGG
> ------------------------------------------------------------------------
> GGG's Profile:
http://www.excelforum.com/member.php...o&userid=29220
> View this thread: http://www.excelforum.com/showthread...hreadid=492029
>

Try...

=SUMPRODUCT(COUNTIF(E5:E41,{"DL380","DL580","RP4400","DL585"}),{2,4,4,4})

or

=SUMPRODUCT(COUNTIF(E5:E41,A1:A4),B1:B4)

....where A1:B4 contains the following table...

DL380 2
DL580 4
RP4400 4
DL585 4

Hope this helps!

In article <[email protected]>,
GGG <[email protected]> wrote:

> I'm using this formula and looking for a way to simplify it.
>
> =SUM((COUNTIF(E5:E41,"DL380")*2),(COUNTIF(E5:E41,"DL580")*4),
> (COUNTIF(E5:E41,"RP4400")*4), (COUNTIF(E5:E41,"DL585")*4))
>
> As more models get added it will become tougher to maintain. I'm
> trying to say count all the cells with "RP4400, DL580 or DL585" in
> them. The list will grow. Is there a way to say count if it matches
> "any value stored in this range"?