sumproduct w/horizontal range not working
 

I am trying to run sumproduct with two criteria, both with a horizontal
range. It does not seem to be working and is returnig a zero count.
Here is what my formula looks like:

=SUMPRODUCT((E1:AS1="*Discussed*")*(E2:AS2="YES"))

The criteria for E1:AS1 (Discussed) is one of several words.

Any help would be greatly appreciated.

Thanks!


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

Myabe:

=SUMPRODUCT(--(SEARCH("Discussed",E1:AS1)0),--(E2:AS2="Yes"))



"dcd123" wrote:


I am trying to run sumproduct with two criteria, both with a horizontal
range. It does not seem to be working and is returnig a zero count.
Here is what my formula looks like:

=SUMPRODUCT((E1:AS1="*Discussed*")*(E2:AS2="YES"))

The criteria for E1:AS1 (Discussed) is one of several words.

Any help would be greatly appreciated.

Thanks!


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

Try...

=SUMPRODUCT(ISNUMBER(SEARCH("Discussed",E1:AS1))*( E2:AS2="Yes"))

Hope this helps!

dcd123 Wrote:
I am trying to run sumproduct with two criteria, both with a horizontal
range. It does not seem to be working and is returnig a zero count.
Here is what my formula looks like:

=SUMPRODUCT((E1:AS1="*Discussed*")*(E2:AS2="YES"))

The criteria for E1:AS1 (Discussed) is one of several words.

Any help would be greatly appreciated.

Thanks!


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

=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"dcd123" wrote in
message ...

I am trying to run sumproduct with two criteria, both with a horizontal
range. It does not seem to be working and is returnig a zero count.
Here is what my formula looks like:

=SUMPRODUCT((E1:AS1="*Discussed*")*(E2:AS2="YES"))

The criteria for E1:AS1 (Discussed) is one of several words.

Any help would be greatly appreciated.

Thanks!


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

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


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

Because SEARCH returns a #VALUE error if the text is not found. Testing this
for 0 still returns #VALUE, whereas testing it for a number, ISNUMBER,
will return FALSE, which is coerced correctly to a number.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"dcd123" wrote in
message ...

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


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

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