Is FREQUENCY the formula I should use?

Bodsy

New Member
Joined
Aug 5, 2010
Messages
21
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
Joined
Jun 25, 2002
Messages
349
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
Joined
Jun 25, 2002
Messages
349
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
Joined
Feb 7, 2008
Messages
411
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
Joined
Jul 26, 2010
Messages
336

ADVERTISEMENT

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

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
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
Joined
Aug 5, 2010
Messages
21

ADVERTISEMENT

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
Joined
Feb 7, 2008
Messages
411
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
Joined
Dec 15, 2005
Messages
1,216
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))))
 
Master Excel Bundle

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

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top