Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need to do a count with several criteria, and am struggling to get it to work:

    =countif(left(Rejections!K2:K5000,2),"11") < or "80" and also Rejections!D25000 = "10" or "40"

    And separately:

    =countif(left(Rejections!K2:K5000,2),"11") < or "80" and also Rejections!D25000 = "10" or "40" and if Rejections!M2:M5000 not ""


    Help please.........

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    =SUMPRODUCT(((Left(Rejections!K2:K5000,2)="11")+(L eft(Rejections!K2:K5000,2)="80"))*((Rejections!D25000 = "10")+(Rejections!D25000 = "40")))

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='764681' date='10-Mar-2009 19:58']Try

    =SUMPRODUCT(((Left(Rejections!K2:K5000,2)="11")+(L eft(Rejections!K2:K5000,2)="80"))*((Rejections!D25000 = "10")+(Rejections!D25000 = "40")))[/quote]


    Thanks, perfect! Where do I add the other condition for the second one?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='VegasNath' post='764684' date='10-Mar-2009 21:24']Thanks, perfect! Where do I add the other condition for the second one?[/quote]
    More "AND" conditions can be created by appending another multiplication.
    More exclusive "OR" conditions can be created by appending an addition.
    You must take great care that the parentheses are placed correctly.

    If the conditions become very complicated, it might be easier to write a custom VBA function.

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans. I take it that this does not work on columns?

    If I try with K:K, I get #NUM, and if I extend the range to include my header, 1:5000, I get #N/A.

    The problem that I have, is that the data is sorted, and moved around using VBA, after which, the range becomes distorted.

    How would I go about writing this formula to the cell using vba, following the sort?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='VegasNath' post='764710' date='10-Mar-2009 23:20'][/quote]
    You cannot use an entire column such as K:K in an array formula or array-like formula with SUMPRODUCT.

    If you'd like help with VBA, please provide enough information.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was thinking:

    With Worksheets("Summary")
    Range("J21").Formula = "=SUMPRODUCT(((LEFT(Rejections!$K$2:$K$5000,2)="11 ")+(LEFT(Rejections!$K$2:$K$5000,2)="80"))*((Rejec tions!$D$2:$D$5000 = "10")+(Rejections!$D$2:$D$5000 = "40")))"

    ...... more of the same

    End With

    But I get Expected end of statement error. ?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your formula contains quotes. To include quotes within a quoted string, you must double them:

    Range("J21").Formula = "=SUMPRODUCT(((LEFT(Rejections!$K$2:$K$5000,2)=""1 1"")+..."

    BTW the line

    With Worksheets("Summary")

    doesn't do anything since you don't refer to it - you should use .Range(...) instead of Range(...)

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks.
    Sorry to be a pain, but any idea what is wrong with this one?

    Range("K21").Formula = "=SUMPRODUCT((((LEFT(Rejections!$K$2:$K$5000,2)="" 11"")+(LEFT(Rejections!$K$2:$K$5000,2)=""80""))*(( Rejections!$D$2:$D$5000=""10"")+(Rejections!$D$2:$ D$5000=""40"")))*((Rejections!$M$2:$M$5000<>"")))"

  10. #10

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You haven't doubled the quotes within the formula at the end.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about:
    range("K21").formula= "=SUMPRODUCT((((LEFT(Rejections!$K$2:$K$5000,2)="" 11"")+(LEFT(Rejections!$K$2:$K$5000,2)=""80""))*(( Rejections!$D$2:$D$5000=""10"")+(Rejections!$D$2:$ D$5000=""40"")))*((Rejections!$M$2:$M$5000<>"""")) )"

    Steve

Posting Permissions

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