Counting how in how many days an item appears, when some items may appear more than once in a day

[DFRNZ1000]

M2222 12/07/12
M2230 12/07/12
M1087 12/06/12
M2525 12/05/12
M2525 12/05/12
M8899 12/05/12

Above is an example of what I am working with. I am trying to get a count of how many times the values on the left appear. But in some cases those values occur more than once in a single day. If they occur once or more in a single day then I want them to count as 1. So if an item occurs three times in one day I want it to be counted once, but if an item occurs once on the 5th and once on the 6th then the count would be at two. Is there a formula I can use to complete this?

Thanks

 

[Snakehips]

DFRNZ1000,

I do not have time to attempt to reolve the below to a single formula.
So this is my best effort to do your count but using 2 'helper' columns.

The count is in column E in the row of the first instance of your column A value.

Excel 2007
	A	B	C	D	E
1	M2222	12/07/2012	M222241102	1	1
2	M2230	12/07/2012	M223041102	1	1
3	M1087	12/06/2012	M108741072	1	1
4	M2525	12/05/2012	M252541041	1	3
5	M2525	12/05/2012	M252541041	0
6	M2525	12/02/2012	M252540951	1
7	M1087	12/06/2012	M108741072	0
8	M2925	12/05/2012	M292541041	1	1
9	M2525	12/01/2012	M252540920	1
10	M2525	12/02/2012	M252540951	0
Sheet3

Cell Formulas
Range	Formula
C1	=A1&B1
D1	=--(MATCH(C1,$C$1:$C$10,0)=ROW(C1))
E1	=IF(MATCH(A1,$A$1:$A$10,0)=ROW(A1),SUMPRODUCT(--($A$1:$A$10=A1)*--($D$1:$D$10=1)),"")

Hope that gives you something to work with.

 

[rallcorn]

There are probably more elegant solutions, but this will work (in several steps)

Assumptions:
Col A is Item Number (header in row 1)
Col B is Date (header in row 1)

Column C flag FIRST occurrence of an item on a given date:
C2 formula =IF(SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2))>1,0,SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2))) **Note placement of "$"
Copy down in Col C

Col D creat list of unique item numbers
D2 formula **NOTE: do not hit enter, use Control+Shift+Enter which will surround the formula with {}
=IF(ROWS($D$2:D2)>SUMPRODUCT(($A$2:$A$10<>"")/COUNTIF($A$2:$A$10,$A$2:$A$10&"")),"",INDEX($A$2:$A$10,MATCH(SMALL(IF(COUNTIF(OFFSET($A$2,0,0,ROW($A$2:$A$10)-ROW($A$2)+1,1),$A$2:$A$10)=1,COUNTIF($A$2:$A$10,"<"&$A$2:$A$10)),ROWS(D$2:$D2)),IF(COUNTIF(OFFSET($A$2,0,0,ROW($A$2:$A$10)-ROW($A$2)+1,1),$A$2:$A$10)=1,COUNTIF($A$2:$A$10,"<"&$A$2:$A$10)),0)))
Copy the formula down in Col D until it begins returning blank results

Col E count items per your description
E2 formula =SUMIFS(C:C,C:C,"1",A:A,D2)
Copy formula down in Col E

 

[DFRNZ1000]

Thank you for all the help! I'm working to get one of these two to work for me

 

[T. Valko]

M2222 12/07/12
M2230 12/07/12
M1087 12/06/12
M2525 12/05/12
M2525 12/05/12
M8899 12/05/12

Above is an example of what I am working with. I am trying to get a count of how many times the values on the left appear. But in some cases those values occur more than once in a single day. If they occur once or more in a single day then I want them to count as 1. So if an item occurs three times in one day I want it to be counted once, but if an item occurs once on the 5th and once on the 6th then the count would be at two. Is there a formula I can use to complete this?

Thanks

It's not real clear what you're wanting to do.

Do you want to count the unique DATES for the items in the left column ?

For example:

The count of unique dates for item M2525 would be 1.

Or, do you want something else?

 

[DFRNZ1000]

I have a list going back a few years with dates of everytime one of these M values occurred. There are about 100 different M values. I am trying to get a count of on how many days each M value occurs. The problem is that on some days a M value may be listed more than once, but I only want it to count as 1. If a M value occurs one or more times on a single day then it counts as one, but if it did not occur then it does not add to the count.

 

[T. Valko]

I have a list going back a few years with dates of everytime one of these M values occurred. There are about 100 different M values. I am trying to get a count of on how many days each M value occurs. The problem is that on some days a M value may be listed more than once, but I only want it to count as 1. If a M value occurs one or more times on a single day then it counts as one, but if it did not occur then it does not add to the count.

Try this...

Book1
	A	B	C	D	E
1	Code	Date	_	Code	Count
2	M1	12/5/2012	_	M1	3
3	M1	12/5/2012	_	M2	1
4	M1	12/9/2012	_	M3	2
5	M1	12/10/2012	_	_	_
6	M2	12/1/2012	_	_	_
7	M3	12/2/2012	_	_	_
8	M3	12/3/2012	_	_	_
9	M3	12/3/2012	_	_	_
10	M3	12/3/2012	_	_	_
Sheet1

With the unique codes in D2:Dn...

This array formula** entered in E2 and copied down:

=SUM(IF(FREQUENCY(IF(A$2:A$10=D2,B$2:B$10),B$2:B$10),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You can easily get the list of unique codes by using advanced filter:

http://contextures.com/xladvfilter01.html#FilterUR