| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting cells that "contain" a text string
How can I make this formula count both "Dev" AND "Dev/RFC" in a column range?
I can't seem to get the wildcard variations (~, &, *) to work with this! =SUMPRODUCT(('2007'!$I$2:$I$300="Quality")*('2007' !$E$2:$E$300="Improvement")*('2007'!$D2:$D$300="De v")) Thanks, Dave -- DSM |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting cells that "contain" a text string
Perhaps this?
=SUMPRODUCT(('2007'!$I$2:$I$300="Quality")*('2007' !$E$2:$E$300="Improvement")*(('2007'!$D2:$D$300="D ev")+('2007'!$D2:$D$300="Dev/RFC"))) or... =SUMPRODUCT(('2007'!$I$2:$I$300="Quality")*('2007' !$E$2:$E$300="Improvement")*(('2007'!$D2:$D$300={" Dev","Dev/RFC"}))) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) "Dave" wrote in message ... How can I make this formula count both "Dev" AND "Dev/RFC" in a column range? I can't seem to get the wildcard variations (~, &, *) to work with this! =SUMPRODUCT(('2007'!$I$2:$I$300="Quality")*('2007' !$E$2:$E$300="Improvement")*('2007'!$D2:$D$300="De v")) Thanks, Dave -- DSM |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting cells that "contain" a text string
I used the second option and it worked.
Thanks, Dave -- DSM "Ron Coderre" wrote: Perhaps this? =SUMPRODUCT(('2007'!$I$2:$I$300="Quality")*('2007' !$E$2:$E$300="Improvement")*(('2007'!$D2:$D$300="D ev")+('2007'!$D2:$D$300="Dev/RFC"))) or... =SUMPRODUCT(('2007'!$I$2:$I$300="Quality")*('2007' !$E$2:$E$300="Improvement")*(('2007'!$D2:$D$300={" Dev","Dev/RFC"}))) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) "Dave" wrote in message ... How can I make this formula count both "Dev" AND "Dev/RFC" in a column range? I can't seem to get the wildcard variations (~, &, *) to work with this! =SUMPRODUCT(('2007'!$I$2:$I$300="Quality")*('2007' !$E$2:$E$300="Improvement")*('2007'!$D2:$D$300="De v")) Thanks, Dave -- DSM |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Consolidating with empty-string ("") cells removed | Excel Worksheet Functions | |||
| In excel counting cells in a range which meet condition "Xand<X" | Excel Worksheet Functions | |||
| cells formatted to tick when text value "Y" if or null if "N" | Excel Discussion (Misc queries) | |||
| Location of the last "s" in a text string | Excel Worksheet Functions | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
Linear Mode
