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.

 

[planetpj]

One way, Download Morefunc which you can find on this board then use this formula (it is an array formula) so you must use

Ctrl+Shift+Enter
=COUNTDIFF(UPPER(IF(($B$2:$B$10=D2),$A$2:$A$10)),TRUE)-1

Book1
	A	B	C	D	E
1
2	1001	9		9	2
3	1001	9		10	1
4	1001	9		11	2
5	1002	9
6	1003	10
7	1004	11
8	1004	11
9	1004	11
10	1005	11
Sheet1

 

[planetpj]

Or one of these formulas that I got off of this site:

=SUM(1/MMULT(--(($B$2:$B$10=D2)*$A$2:$A$10=TRANSPOSE(($B$2:$B$10=D2)*$A$2:$A$10)),ROW($B$2:$B$10)^0))-1+AND($B$2:$B$10=D2)


=SUM(IF(FREQUENCY(IF(($B$2:$B$10=D2)*($A$2:$A$10<>""),MATCH($A$2:$A$10,$A$2:$A$10,0),""),IF(($B$2:$B$10=D2)*($A$2:$A$10<>""),MATCH($A$2:$A$10,$A$2:$A$10),""))>0,1))

 

[Mike Szczesny]

Hi bodsy,
Here is a variation of planetpj's frequency function. Assume 9, 10, 11 are in cells a12:a14. In B12 enter=sum(if(frequency(if($b$2:$b$10=a12,if($a$2:$a$12<>"",match($a$2:$a$10,$a$2:$a$10,0))),row($a$2:$a$10)-row($a$2)+1)>0,1)). Use C-S-E enter. Do not use $for a12. Copy down.

Mike Szczesny
HTH

 

[hrithik]

I recommend using FREQUENCY function, it is much easier than solutions provided by planetpj and mike.

Follow these steps:
1. Highlight a vertical range of cells in which you wish the analysis to be reported (E2:E4)
2. With the whole output range highlighted, type the formula.
= FREQUENCY(B2:B10, D2:D4)
3. Do not press enter. This is an array function and therefore requires you to use the alternative key strokes of ctrl + shift + enter<CTRL><SHIFT> <ENTER>

The results will then be automatically entered in E2:E4

 

[mgirvin]

Maybe this (formulas in green cells):

Excel Workbook
	A	B	C	D	E
1	Order #	Time Stamp		Count Unique Time Stamp
2	1001	9		3
3	1001	9		Extract Unique List of Time Stamp	Unique Count Time Stamp
4	1001	9		9	2
5	1002	9		10	1
6	1003	10		11	2
7	1004	11		.	.
8	1004	11		.	.
9	1004	11		.	.
10	1005	11
...

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

=SUMPRODUCT(--(FREQUENCY(B2:B10,B2:B10)>0))


Enter this formula with Ctrl + Shift + Enter (not just Enter) in cell D4 and copy down:

=IF(ROWS(D$4:D4)>$D$2,"",INDEX($B$2:$B$10,SMALL(IF(FREQUENCY($B$2:$B$10,$B$2:$B$10),ROW($B$2:$B$10)-ROW($B$2)+1),ROWS(D$4:D4))))


Enter this formula with Ctrl + Shift + Enter (not just Enter) in cell E4 and copy down:

=IF(D4="","",SUM(--(FREQUENCY(IF($B$2:$B$10=D4,$A$2:$A$10),$A$2:$A$10)>0)))


Here is a picture of the formulas:

Excel Workbook
	D
2	3
...

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(--(FREQUENCY(B2:B10,B2:B10)>0))

Excel Workbook
	D
4	9
...

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Excel Workbook
	E
4	2
...

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Bodsy]

Hi Mike

Many thanks for your reply - this works perfectly! I will now spend the next few months trying to figure out how!!

Bodsy

 

[Mike Szczesny]

Hi Bodsy,

Refer to Mike Girvin's index formula. You can also incorporate the match function into his formula if needed. This would produce those 9,10, 11 results from that universe of items.
Mike

 

[mgirvin]

Mike,

I may be wrong about this, but as I see it, we do not need the MATCH because the source data in both columns are numbers and so the FREQUENCY functions handles it perfectly. FREQUENCY also ignores blanks and text. This formula extracts the 9, 10, 11 without the MATCH:

=IF(ROWS(D$4:D4)>$D$2,"",INDEX($B$2:$B$10,SMALL(IF(FREQUENCY($B$2:$B$10,$B$2:$B$10),ROW($B$2:$B$10)-ROW($B$2)+1),ROWS(D$4:D4))))

 

[mgirvin]

Bodsy,

It may take a while to learn about unique count and data extract formulas, but this 50 minute video I made about the many uses of the FREQUENCY function may speed the process up:


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