Identifying unique days in excel, with multiple identifiers

I'm trying to get Excel to determine the number of unique days in a week, based on an Identifier.
I'm pulling in data from SQL and pasting it into a sheet. Here's an example

10045 0 0 0 0 0 1 1
10045 1 1 0 1 1 0 0
10046 0 0 0 1 0 0 0
10046 1 0 0 0 0 0 0
10047 0 0 0 1 1 0 0
10047 1 1 0 0 1 0 0
10049 1 1 0 1 1 1 0
10050 0 0 0 0 0 1 1
10050 1 0 0 0 0 0 0
10050 1 1 0 1 1 0 0
10051 1 0 0 0 0 0 0
10051 0 0 0 0 1 0 0
10051 0 1 0 1 0 0 0

The first column is the ID, the remaining columns are the days of the week Mon-Sun. Each day is selected if the flag is set to true.
So ID 10045 will have 6 unique days. A better example is ID 10050, which also has 6 unique days, since Wednesday is not set to true on any of the rows.

I want to add another column which determines the number of unique days for each identifier. I've done some searching for the solution to this, but have been unable to find it.

If there's any additional information you might need let me know.
Thank you

I found a similar post in the forum here: http://www.excelforum.com/excel-gene...ue-values.html

The formula suggested there is CSE formula =SUM(IF($C$2:$C$101=C2,1/COUNTIF($A$2:$A$101,$A$2:$A$101)))
-- so if I were to apply it to my situation I'm guessing it would look like: CSE formula =SUM(IF(B$1:B$101=B1,1/COUNTIF($A$1:$A$101,$A$1:$A$101)))

This still doesn't seem to be working for me. Forgive my lack of knowledge in this area.

Attached is the actual sheet I'm working on.

unique days.xlsx

Will you have a separate list of unique IDs, or do you want to show the number of unique days on each row, so that you would have 6 on the first 2 rows, then 2 on the next 2 rows, and so on?

Pete

I think showing the number on each row would be helpful.
This isn't the entire document. There are other sheets, and I would probably do a =vlookup on those other sheets to apply the result of unique days to the other sheets.

So yes, in the example. 6 on the first 2 rows, 2 on the next 2 rows, and so on like you said.

Okay, you can put this formula in I1:

=SUMPRODUCT(--(B1:H1*10^(8-COLUMN($B1:H1))))

and copy down - this converts those individual 1s and 0s to a composite number made up of just 1s and 0s. Then you can put this formula in J1:

=LEN(--SUBSTITUTE(SUBSTITUTE(SUMIF(A:A,A1,I:I),"2","1"),"3","1"))-LEN(SUBSTITUTE(--SUBSTITUTE(SUBSTITUTE(SUMIF(A:A,A1,I:I),"2","1"),"3","1"),"1",""))

and copy this down - this gives you the number of unique days for the ID in column A.

Hope this helps.

Pete

Pete that was perfect. Thank you so much.

There's one other problem I have within the same table. I'm going to spend some time trying to figure out how to make it work by using the formulas you just gave me, although it's way different than anything I would have put together. But I don't want to be just begging for other people to do my work for me.

If I get stumped on this problem I'll update this post and ask for help again.

Thanks again Pete, mad props