dcd123 Wrote:
Thank you. It did work! Can you explain why the ISNUMBER and SEARCH
made the difference?

SUMPRODUCT doesn't accept wildcards. ISNUMBER/SEARCH is a way of
achieving the same result. Let's assume that E1:I2 contains the
following data...


Code:
--------------------
To_be_discussed x y To_be_discussed z
Yes No No Yes Yes
--------------------


...and that we have the following formula...


Code:
--------------------
=SUMPRODUCT(ISNUMBER(SEARCH("Discussed",E1:I1))*(E 2:I2="Yes"))
--------------------


SEARCH("Discussed",E1:I1) returns the following array of values...


Code:
--------------------
{7,#VALUE!,#VALUE!,7,#VALUE!}

Note that SEARCH returns a #VALUE! error when the text being searched is not found.
--------------------


(ISNUMBER(SEARCH("Discussed",E1:AS1))) returns the following array of
values...


Code:
--------------------
{TRUE,FALSE,FALSE,TRUE,FALSE}
--------------------


(E2:AS2="Yes") returns the following array of values...


Code:
--------------------
{TRUE,FALSE,FALSE,TRUE,TRUE}
--------------------


SUMPRODUCT then multiplies the two arrays...


Code:
--------------------
(ISNUMBER(SEARCH("Discussed",E1:I1))*(E2:I2="Yes")
--------------------


...and returns the folloiwng...


Code:
--------------------
{1,0,0,1,0}
--------------------


...which is summed, and returns 2. Note that numerical equivalent of
TRUE and FALSE is 1 and 0, respectively.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=397880