Results 1 to 4 of 4

Thread: Sum Product Question - Count if multiple columns are not equal
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2014
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sum Product Question - Count if multiple columns are not equal

    I've got a large sheet of data with multiple columns. Need to count the number of items that meet certain criteria. For example I wanted to see if the two sets of data met 2 criteria and if so to count them.

    {=SUMPRODUCT(IF(Sheet2!C:C>0,IF(Sheet2!F:F=A4,IF(Sheet2!B:B=B$2,--(Sheet2!C:C=Sheet2!E:E)))))}

    This works perfectly.

    Now I want to do the exact same count only I want to know when sheet2!C:C <> Sheet2!E:E

    When I change the formula to this

    {=SUMPRODUCT(IF(Sheet2!C:C>0,IF(Sheet2!F:F=A4,IF(Sheet2!B:B=B$2,--(Sheet2!C:C<>Sheet2!E:E)))))}

    I am getting 0 even though I know there are 34 items that should show up in this calculation.

    Yes I'm aware that doing the array for the whole column is slowing down the calculation, I've just accepted it for this so I can add data if needed.

    This appears to work. Anyone able to help why a sumifs is fine with numbers as text but sumproduct cannot identify that text 2010= number 2010?
    Last edited by dhsilv2; Sep 9th, 2014 at 02:27 PM.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Sum Product Question - Count if multiple columns are not equal

    What kind of data do you have in column C?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Board Regular
    Join Date
    Jul 2014
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sum Product Question - Count if multiple columns are not equal

    Quote Originally Posted by Aladin Akyurek View Post
    What kind of data do you have in column C?
    C was 0-4 and I wanted to not count 0's. The issue was with column B which was a year stored as '2010 instead of 2010. I started with this as a countifs but the requirements became such I couldn't use the ifs statement. I rarely use sumproduct and I guess it's more sensitive to numbers stored as text? And I wasn't able to turn the countifs into an array of column C does or does not equal A. I'd much rather do a countifs for countless reasons.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Sum Product Question - Count if multiple columns are not equal

    Quote Originally Posted by dhsilv2 View Post
    C was 0-4 and I wanted to not count 0's. The issue was with column B which was a year stored as '2010 instead of 2010. I started with this as a countifs but the requirements became such I couldn't use the ifs statement. I rarely use sumproduct and I guess it's more sensitive to numbers stored as text? And I wasn't able to turn the countifs into an array of column C does or does not equal A. I'd much rather do a countifs for countless reasons.
    1. Control+shift+enter (CSE), not just enter:
    Code:
    =SUM(IF(Sheet2!$C$2:$C$1000>0,IF(Sheet2!$F$:$F$1000=A4,
      IF(Sheet2!$B$2:$B$1000+0=B$2,IF(Sheet2!$C$2:$C$1000=Sheet2!$E$2:$E$1000,1)))))
    
    where B2 is a number.

    2. Again CSE:
    Code:
    =SUM(IF(Sheet2!$C$2:$C$1000>0,IF(Sheet2!$F$:$F$1000=A4,
      IF(Sheet2!$B$2:$B$1000+0=B$2,IF(1-(Sheet2!$C$2:$C$1000=Sheet2!$E$2:$E$1000),1)))))
    
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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