# Problem SUMIF-ing on an array

1. ## 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. ## Re: Problem SUMIF-ing on an array

Hi

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

3. ## Re: Problem SUMIF-ing on an array

Originally Posted by Fotis1991
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. ## Re: Problem SUMIF-ing on an array

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

5. ## 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. ## 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. ## 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

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