Is FREQUENCY the formula I should use?

[Bodsy]

This is my first post, so I hope this is the correct way to do things!

I have a table of data with 2 columns.

Column 1 contains order numbers.

Column 2 contains a time stamp (the hour in which the order number occurred).

I want to make a count of the number of distinct orders that happened in each hour.

Normally a Countif would work, however the data has been produced in such a way that if an order has three items in it, there are three occurrences of the Order number.

Column A ____ Column B
1001 _________ 9
1001 _________ 9
1001 _________ 9
1002 _________ 9
1003 _________ 10
1004 _________ 11
1004 _________ 11
1004 _________ 11
1005 _________ 11

The result I would be looking for is:

Time _____ Count of Order Numbers
9 _________ 2
10 _________ 1
11 _________ 2

Any help would be hugely appreciated.

 

[Bodsy]

I haven't tried the Match route, but Mike Girvin's formula works fine as is. Does the Match route allow even more conditions to be applied?

Bodsy

 

[Mike Szczesny]

Hi Mike,

I am by no means an expert about these frequency functions. Thanks for your clarfication and conclusions on this. I am still figuring out the nuances of this very useful function. I have learned so much from your videos.
Thanks,
Mike

 

[mgirvin]

I am really not an expert either, just having fun with Excel. But if you want to be sickened by 50 minutes of non-stop FREQUENCY function minutia, try that video. The video is just a compilation of the things I teach in my statistics class and the things I have learned from Excel Gurus like Aladin and Domenic and pgc01 and other FREQUENCY masters!

 

[Bodsy]

Mr Girvin

Despite the likelihood of looking stupid, could you enlighten me on the section of the formula Sum(--(~)>0))?

What is the SUM doing? I kind of understand the Frequency section, but when the time is 9, the answer becomes 3 without this part, so what happens in this section to reduce that number from 3 to 2?

What does "--" do specifically?
Bodsy

 

[mgirvin]

The FREQUENCY delivers a count for each unique item, like this for example:

{0,3,0,1}

The > converts the numbers to TRUE or FALSE:

{0,3,0,1}>0 becomes {FALSE,TRUE,FALSE,TRUE}

The -- converts TRUE to 1 and FALSE to 0:

--{FALSE,TRUE,FALSE,TRUE} becomes {0,1,0,1}

The SUM adds:

=SUM({0,1,0,1}) becomes 2

The video goes into even more detail, and it has pictures to match the words.

I hope that helps.

 

[Mike Szczesny]

Hi Mike Girvin,

Can you help me on this variation of the frequency function. What are the formulas to find the sum(quantity) of unique combinations of cells. Also how would you display these combinations? Please refer to my example.
Thanks,
Mike Szczesny

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 74px"><COL style="WIDTH: 74px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD style="BORDER-BOTTOM: #f0f0f0 3px dotted; BORDER-LEFT: #f0f0f0 3px dotted; BORDER-TOP: #f0f0f0 3px dotted; BORDER-RIGHT: #f0f0f0 3px dotted">A</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px dotted; BORDER-LEFT: #f0f0f0 3px dotted; BORDER-TOP: #f0f0f0 3px dotted; BORDER-RIGHT: #f0f0f0 3px dotted">B</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px dotted; BORDER-LEFT: #f0f0f0 3px dotted; BORDER-TOP: #f0f0f0 3px dotted; BORDER-RIGHT: #f0f0f0 3px dotted">C</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px dotted; BORDER-LEFT: #f0f0f0 3px dotted; BORDER-TOP: #f0f0f0 3px dotted; BORDER-RIGHT: #f0f0f0 3px dotted">D</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px dotted; BORDER-LEFT: #f0f0f0 3px dotted; BORDER-TOP: #f0f0f0 3px dotted; BORDER-RIGHT: #f0f0f0 3px dotted">E</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px dotted; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px dotted; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px dotted; BORDER-RIGHT: #f0f0f0 3px dotted">1</TD><TD>Desired result : 3 unique combinations</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px dotted; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px dotted; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px dotted; BORDER-RIGHT: #f0f0f0 3px dotted">2</TD><TD>a</TD><TD>b </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px dotted; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px dotted; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px dotted; BORDER-RIGHT: #f0f0f0 3px dotted">3</TD><TD>a</TD><TD>b </TD><TD> </TD><TD>a</TD><TD>b</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px dotted; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px dotted; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px dotted; BORDER-RIGHT: #f0f0f0 3px dotted">4</TD><TD>a</TD><TD>b </TD><TD> </TD><TD>a</TD><TD>c</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px dotted; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px dotted; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px dotted; BORDER-RIGHT: #f0f0f0 3px dotted">5</TD><TD>a</TD><TD>c </TD><TD> </TD><TD>a</TD><TD>d</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px dotted; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px dotted; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px dotted; BORDER-RIGHT: #f0f0f0 3px dotted">6</TD><TD>a</TD><TD>b </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px dotted; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px dotted; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px dotted; BORDER-RIGHT: #f0f0f0 3px dotted">7</TD><TD>a</TD><TD>d </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px dotted; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px dotted; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px dotted; BORDER-RIGHT: #f0f0f0 3px dotted">8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

 

[mgirvin]

Maybe this:

Here is the full set of data and formulas (formulas in green cells):

Excel Workbook
	A	B	C	D	E	F	G	H
1	Column1	Column2		2 Column Unique Count		Extract Unique Based On 2 Columns
2	a	b		3		a	b
3	a	b				a	c
4	a	b				a	d
5	a	c				.	.
6	a	b				.	.
7	a	d				.	.
8						.	.
...

Enter this with Ctrl + Shift + Enter (not just Enter) into cell D2:

=SUM(IF(FREQUENCY(IF($A$2:$A$7&$B$2:$B$7<>"",MATCH("~"&$A$2:$A$7&$B$2:$B$7,$A$2:$A$7&$B$2:$B$7&"",0)),ROW($A$2:$A$7)-ROW($A$2)+1),1))

Enter this with Ctrl + Shift + Enter (not just Enter) into cell F2 and copy to the right and down:

=IF(ROWS(F$2:F2)>$D$2,"",INDEX(A$2:A$7,SMALL(IF(FREQUENCY(IF($A$2:$A$7&$B$2:$B$7<>"",MATCH("~"&$A$2:$A$7&$B$2:$B$7,$A$2:$A$7&$B$2:$B$7&"",0)),ROW($A$2:$A$7)-ROW($A$2)+1),ROW($A$2:$A$7)-ROW($A$2)+1),ROWS(F$2:F2))))

Does that work?

 

[Mike Szczesny]

Hi Mike,
That's exactly what I needed. It makes perfect sense as a variation of the frequency function . I was thinking along the lines of that, but was not yet fully there on this adaptation. Thanks for clearing it for me
Mike

 

[mgirvin]

I am glad that it helped. It is funny you posted this question because just a few days ago I posted a video on this topic:

http://www.youtube.com/watch?v=AFoocLEO124