| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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"? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Colomn of strings, how to count each string with "abc" OR "def" | Excel Worksheet Functions | |||
| How can I count strings within strings | Excel Worksheet Functions | |||
| Wildcard MATCH() breaks on long (?) strings | Excel Worksheet Functions | |||
| Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions | |||
| If two columns match then count one. How? Tried countif and sum . | Excel Worksheet Functions | |||
Linear Mode
