+ Reply to Thread
Results 1 to 5 of 5

Sumproduct - 2 criteria with different array sizes

  1. #1
    Registered User
    Join Date
    03-13-2012
    Location
    Burlington Ontario
    MS-Off Ver
    Excel 2010
    Posts
    51

    Sumproduct - 2 criteria with different array sizes

    I have a range of cells conditionally formatted to highlight cells in column e that are not blank and less than the corresponding row in column a, AND column b equals either "YES", or "N/A". But I haven't been able to convert that formula into something that will give me the number of times this condition exists in a given range.
    Each of the arguments in my formula provide the correct number of true/false answers when evaluated on their own

    =SUMPRODUCT((E6:E64<A6:A64,B6:B64="N/A")+(E6:E64<A6:A64,B6:B64="YES"))

    Col. A Col. B Col. E
    1/1/11 N/A 31-Dec-11
    1/1/11 N/A 31-Dec-11
    1/1/11 N/A 31-Dec-11
    8/7/12 N/A
    8/7/12 N/A
    6/24/11 NO
    7/24/12 NO
    9/23/11 YES 31-Dec-11
    1/1/11 N/A 31-Dec-11
    9/24/12 N/A 31-Dec-11
    10/4/11 N/A
    Last edited by Lacaycer; 10-03-2012 at 02:24 PM.

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Sumproduct - 2 criteria with different array sizes

    Hello
    Using your sample data I get the answer to 2, when using the following formula:

    Please Login or Register  to view this content.
    Is that correct?

    Hope this helps.
    DBY

  3. #3
    Registered User
    Join Date
    03-13-2012
    Location
    Burlington Ontario
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: Sumproduct - 2 criteria with different array sizes

    It works better than anything I did, but it's not working right. In the supplied range it returned a value of 2. The 4th last line should not have been counted, the date in column e is greater than the date in column a

  4. #4
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Sumproduct - 2 criteria with different array sizes

    Yes you're correct, I think I missed out a couple of brackets, try:

    Please Login or Register  to view this content.
    I now get the answer of 1.

    DBY

  5. #5
    Registered User
    Join Date
    03-13-2012
    Location
    Burlington Ontario
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: Sumproduct - 2 criteria with different array sizes

    Bingo, Thanks for your help

+ 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