+ Reply to Thread
Results 1 to 7 of 7

3 criteria

  1. #1
    Mike B
    Guest

    3 criteria


    My old formula worked fine for finding the number based on two
    criteria,
    =SUMPRODUCT(-(J124:J1111=0),-(AV124:AV1111="yes"))

    Now I want to add a third criteria,
    =SUMPRODUCT(--(AV124:AV1111="yes"),--(E123:E1111"Half
    Off"),--(J124:J1111=0),J124:J1111)

    But now I get a #VALUE error....any ideas?


    --
    Mike B

  2. #2
    Biff
    Guest

    Re: 3 criteria

    --(E123:E1111"Half Off")

    Unless those are typos:

    That array isn't the same size as the others and you're missing the = sign.

    Biff

    "Mike B" <[email protected]> wrote in message
    news:[email protected]...
    >
    > My old formula worked fine for finding the number based on two
    > criteria,
    > =SUMPRODUCT(-(J124:J1111=0),-(AV124:AV1111="yes"))
    >
    > Now I want to add a third criteria,
    > =SUMPRODUCT(--(AV124:AV1111="yes"),--(E123:E1111"Half
    > Off"),--(J124:J1111=0),J124:J1111)
    >
    > But now I get a #VALUE error....any ideas?
    >
    >
    > --
    > Mike B




  3. #3
    JE McGimpsey
    Guest

    Re: 3 criteria

    First, all your ranges need to be the same size. Probably need to change
    E123 to E124.

    Second, since you're multiplying by the range J124:J1111, but only if
    J124:J1111=0, you can replace the whole formula with

    0

    In article <[email protected]>,
    Mike B <[email protected]> wrote:

    > My old formula worked fine for finding the number based on two
    > criteria,
    > =SUMPRODUCT(-(J124:J1111=0),-(AV124:AV1111="yes"))
    >
    > Now I want to add a third criteria,
    > =SUMPRODUCT(--(AV124:AV1111="yes"),--(E123:E1111"Half
    > Off"),--(J124:J1111=0),J124:J1111)
    >
    > But now I get a #VALUE error....any ideas?


  4. #4
    JE McGimpsey
    Guest

    Re: 3 criteria

    First, all your ranges need to be the same size. Probably need to change
    E123 to E124.

    Second, since you're multiplying by the range J124:J1111, but only if
    J124:J1111=0, you can replace the whole formula with

    0

    In article <[email protected]>,
    Mike B <[email protected]> wrote:

    > My old formula worked fine for finding the number based on two
    > criteria,
    > =SUMPRODUCT(-(J124:J1111=0),-(AV124:AV1111="yes"))
    >
    > Now I want to add a third criteria,
    > =SUMPRODUCT(--(AV124:AV1111="yes"),--(E123:E1111"Half
    > Off"),--(J124:J1111=0),J124:J1111)
    >
    > But now I get a #VALUE error....any ideas?


  5. #5
    Mike B
    Guest

    Re: 3 criteria


    Sorry, couple really stupid errors in my original post. How do I add a
    third criteria to this formula:
    =SUMPRODUCT(-(J124:J1111=1),-(AV124:AV1111="yes"))

    This just doesn't work:
    =SUMPRODUCT(-(J124:J1111=1),-(AV124:AV1111="yes"),-(V124:V1111="Half
    Off"))

    Thanks



    JE McGimpsey Wrote:
    > First, all your ranges need to be the same size. Probably need to
    > change
    > E123 to E124.
    >
    > Second, since you're multiplying by the range J124:J1111, but only if
    > J124:J1111=0, you can replace the whole formula with
    >
    > 0
    >
    > In article [email protected],
    > Mike B [email protected] wrote:
    >
    > My old formula worked fine for finding the number based on two
    > criteria,
    > =SUMPRODUCT(-(J124:J1111=0),-(AV124:AV1111="yes"))
    >
    > Now I want to add a third criteria,
    > =SUMPRODUCT(--(AV124:AV1111="yes"),--(E123:E1111"Half
    > Off"),--(J124:J1111=0),J124:J1111)
    >
    > But now I get a #VALUE error....any ideas?



    --
    Mike B

  6. #6
    JE McGimpsey
    Guest

    Re: 3 criteria

    Hard to tell without knowing what "just doesn't work" means.
    Sytactically, it's fine, though if you're counting, you'll get a
    negative number - add another negation to make it positive.



    In article <[email protected]>,
    Mike B <[email protected]> wrote:

    > Sorry, couple really stupid errors in my original post. How do I add a
    > third criteria to this formula:
    > =SUMPRODUCT(-(J124:J1111=1),-(AV124:AV1111="yes"))
    >
    > This just doesn't work:
    > =SUMPRODUCT(-(J124:J1111=1),-(AV124:AV1111="yes"),-(V124:V1111="Half
    > Off"))


  7. #7
    Mike B
    Guest

    Re: 3 criteria


    Thanks for your help JE. I had some caffine, retyped my formula, added
    another negation, and it worked fine.

    Thanks again
    mike



    JE McGimpsey Wrote:
    > Hard to tell without knowing what "just doesn't work" means.
    > Sytactically, it's fine, though if you're counting, you'll get a
    > negative number - add another negation to make it positive.
    >
    >
    >
    > In article [email protected],
    > Mike B [email protected] wrote:
    >
    > Sorry, couple really stupid errors in my original post. How do I add
    > a
    > third criteria to this formula:
    > =SUMPRODUCT(-(J124:J1111=1),-(AV124:AV1111="yes"))
    >
    > This just doesn't work:
    > =SUMPRODUCT(-(J124:J1111=1),-(AV124:AV1111="yes"),-(V124:V1111="Half
    > Off"))



    --
    Mike B

+ 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