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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

planetpj

Active Member
Joined
Jun 25, 2002
Messages
344
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
344
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,213
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,213
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))))
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,693
Messages
5,512,911
Members
408,920
Latest member
KLH81

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top