# Is FREQUENCY the formula I should use?

#### Bodsy

##### New Member
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.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### planetpj

##### Active Member
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
ABCDE
1
21001992
310019101
410019112
510029
6100310
7100411
8100411
9100411
10100511
Sheet1

#### planetpj

##### Active Member
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

##### Active Member
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

##### Active Member

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

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

##### Well-known Member
Maybe this (formulas in green cells):
Excel Workbook
ABCDE
1Order #Time StampCount Unique Time Stamp
2100193
310019Extract Unique List of Time StampUnique Count Time Stamp
41001992
510029101
6100310112
7100411..
8100411..
9100411..
10100511
...

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
23
...
Cell Formulas
RangeFormula
D2=SUMPRODUCT(--(FREQUENCY(B2:B10,B2:B10)>0))
Excel Workbook
D
49
...
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Excel Workbook
E
42
...
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

#### Bodsy

##### New Member

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

##### Active Member
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

##### Well-known Member
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

##### Well-known Member
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:

Replies
5
Views
531
Replies
7
Views
182
Replies
3
Views
222
Replies
8
Views
262
Replies
5
Views
280 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,421
Messages
5,831,520
Members
430,075
Latest member
Francis101 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back