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!
Try...
=SUMPRODUCT(ISNUMBER(SEARCH("Discussed",E1:AS1))*(E2:AS2="Yes"))
Hope this helps!
Originally Posted by dcd123
Thank you. It did work! Can you explain why the ISNUMBER and SEARCH made the difference?
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
>
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
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...
...and that we have the following formula...
Please Login or Register to view this content.
SEARCH("Discussed",E1:I1) returns the following array of values...Please Login or Register to view this content.
(ISNUMBER(SEARCH("Discussed",E1:AS1))) returns the following array of values...Please Login or Register to view this content.
(E2:AS2="Yes") returns the following array of values...Please Login or Register to view this content.
SUMPRODUCT then multiplies the two arrays...Please Login or Register to view this content.
...and returns the folloiwng...Please Login or Register to view this content.
...which is summed, and returns 2. Note that numerical equivalent of TRUE and FALSE is 1 and 0, respectively.Please Login or Register to view this content.
Hope this helps!
Last edited by Domenic; 08-22-2005 at 07:00 PM.
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
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
>
>
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
>
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
>
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
>
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
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
>
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
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
>
>
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
>
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
>
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
>
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
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
>
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
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
>
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
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
>
>
=SUMPRODUCT(--(ISNUMBER(SEARCH("Discussed",E1:AS1))),--(E2:AS2="YES"))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"dcd123" <dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com> wrote in
message news:dcd123.1u6jfx_1124738143.3762@excelforum-nospam.com...
>
> 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
>
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" <dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com> wrote in
message news:dcd123.1u6m6m_1124741195.6834@excelforum-nospam.com...
>
> 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
>
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
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks