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

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

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