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"?


--
GGG
------------------------------------------------------------------------
GGG's Profile: http://www.excelforum.com/member.php...o&userid=29220
View this thread: http://www.excelforum.com/showthread...hreadid=492029

Count if match any of 3 strings
 

A couple of ways

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

or


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


--

Regards,

Peo Sjoblom

"GGG" wrote in message
...

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

Count if match any of 3 strings
 

Try...

=SUMPRODUCT(COUNTIF(E5:E41,{"DL380","DL580","RP440 0","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 ,
GGG 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"?