+ Reply to Thread
Results 1 to 7 of 7

Problem SUMIF-ing on an array

  1. #1
    Registered User
    Join Date
    09-30-2012
    Location
    Tel-Aviv
    MS-Off Ver
    Excel 2010
    Posts
    84

    Problem SUMIF-ing on an array

    Hello all,

    I have a data set: each row belongs to a contact in a company. There are many companies, and for most companies - multiple contacts.
    For each contact there's a column with 1s or 0s : 1 indicates the contact posses a certain quality (e.g. the contact belongs to the finance department). There can be multiple contacts in each company that posses the quality - but there could also be companies with no contacts at all that have 1s.

    What I'm trying to find is which companies have no contacts that posses that certain quality. Which companies have no contacts with 1s.

    What I did:
    Started a new sheet, with unique company names.
    in the cell next to the first company's name, I ran:
    =Sumif( column with all the companies in the original data set, name of company from the 1st column, column of 0s or 1s that describe that quality in the original set)
    I of course locked the first and last components with F4.
    then ran it as an array.
    and of course dragged the formula to each unique company name.

    The problem:
    * Trying to sort the sum column from small to large (so I could see which companies have 0 contacts with the quality) just didn't work. If you stroll down the column you can see it was not sorted.
    * Trying to filter for just rows with 0s in the sum column, yields incorrect results. Checking in the original data set reveals some of these companies' contacts actually have 1s.

    This problem has been repeating itself on several data sets.

    What am I doing wrong?

    (I should mention that the 1s and 0s that represnt that quality in the original dataset are formatted as general or number (tried both) and not as text - so that can't be the reason)

    Thanks a lot all,
    I'm definitely banging my head on my desk for a few good hours

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Problem SUMIF-ing on an array

    Hi

    I think that will be usfull for all if you upload a small sample workbook.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    09-30-2012
    Location
    Tel-Aviv
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Problem SUMIF-ing on an array

    Quote Originally Posted by Fotis1991 View Post
    Hi

    I think that will be usfull for all if you upload a small sample workbook.

    Yes, of course.
    However I'm unable to do so due to the sensitivity of the data...

    I was wondering if someone experienced similar problems or if this is common mistake by users

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Problem SUMIF-ing on an array

    Create a SAMPLE sheet, with data to looks like yours.

    Of course don't upload your real data.

  5. #5
    Registered User
    Join Date
    09-30-2012
    Location
    Tel-Aviv
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Problem SUMIF-ing on an array

    The weird thing is, when I sort the sumif column from small to large, while selecting both columns of course, the criteria in the sumif column stops matching the values in the adjacent cell to the left

  6. #6
    Registered User
    Join Date
    09-30-2012
    Location
    Tel-Aviv
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Problem SUMIF-ing on an array

    I do not have the time to generate an obfuscated sample - I will do so shortly. Admins - please keep the thread open. Thanks

  7. #7
    Registered User
    Join Date
    09-30-2012
    Location
    Tel-Aviv
    MS-Off Ver
    Excel 2010
    Posts
    84

    Re: Problem SUMIF-ing on an array

    Well, the solution to the problem was found. It's suboptimal but it works.
    Basically, the column that holds that data regarding the existense of the specific quality was a formula. Once I changed the cells to values, the problem was solved. It's messy, and kinddog beats the point of having dynamically updated spreadsheet.

    Can you better understand this issue now and perhaps advise why this happens?

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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