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