+ Reply to Thread
Results 1 to 67 of 67

sumproduct w/horizontal range not working

  1. #1
    Registered User
    Join Date
    07-20-2005
    Posts
    22

    Arrow 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!

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Try...

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

    Hope this helps!

    Quote Originally Posted by dcd123
    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!

  3. #3
    Registered User
    Join Date
    07-20-2005
    Posts
    22

    Thumbs up

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

  4. #4
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  5. #5
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  6. #6
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Quote Originally Posted by dcd123
    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...

    Please Login or Register  to view this content.
    ...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.

    Hope this helps!
    Last edited by Domenic; 08-22-2005 at 07:00 PM.

  7. #7
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  8. #8
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  9. #9
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  10. #10
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  11. #11
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  12. #12
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  13. #13
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  14. #14
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  15. #15
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  16. #16
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  17. #17
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  18. #18
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  19. #19
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  20. #20
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  21. #21
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  22. #22
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  23. #23
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  24. #24
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  25. #25
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  26. #26
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  27. #27
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  28. #28
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  29. #29
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  30. #30
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  31. #31
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  32. #32
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  33. #33
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  34. #34
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  35. #35
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  36. #36
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  37. #37
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  38. #38
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  39. #39
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  40. #40
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  41. #41
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  42. #42
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  43. #43
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  44. #44
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  45. #45
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  46. #46
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  47. #47
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  48. #48
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  49. #49
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  50. #50
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  51. #51
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  52. #52
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  53. #53
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  54. #54
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  55. #55
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  56. #56
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  57. #57
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  58. #58
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  59. #59
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  60. #60
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  61. #61
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  62. #62
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  63. #63
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  64. #64
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


  65. #65
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

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

    --

    HTH

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


    "dcd123" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  66. #66
    Bob Phillips
    Guest

    Re: sumproduct w/horizontal range not working

    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" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  67. #67
    Duke Carey
    Guest

    RE: sumproduct w/horizontal range not working

    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
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1