Frequency Function

Zakarni · Jun 17, 2022

Dear Excel Guru,

I have a data set with two columns, one for customer numbers from 1 to 50 customers and 2nd column for the number of books sold per customer.

How can I get the frequency of numbers of books sold per customer, please?

Date	Customer Number	Books sold
7/14/2008	1	2
7/21/2008	1	5
7/28/2008	1	6
8/11/2008	1	5
8/18/2008	1	4
8/25/2008	1	3
9/1/2008	1	6
9/15/2008	1	4
9/21/2008	1	6
10/27/2008	1	4
11/10/2008	1	3
11/17/2008	1	4
11/24/2008	1	5
12/8/2008	1	1
12/15/2008	1	8
12/22/2008	1	6
1/5/2009	1	6
1/12/2009	1	6
1/19/2009	1	1
1/26/2009	1	7
2/9/2009	1	3
2/16/2009	1	7
2/23/2009	1	2
3/9/2009	1	5
3/16/2009	1	4
4/20/2009	1	3
5/18/2009	1	3
6/8/2009	1	5
6/15/2009	1	4
6/22/2009	1	5
6/29/2009	1	5
7/13/2009	1	5
7/20/2009	1	4
7/27/2009	1	7
8/10/2009	1	3
8/17/2009	1	3
8/24/2009	1	5
8/31/2009	1	5
10/12/2009	1	6
10/19/2009	1	3
10/26/2009	1	4
11/9/2009	1	4
11/16/2009	1	1
11/23/2009	1	4
11/30/2009	1	0
9/21/2008	2	6
10/27/2008	2	5
11/10/2008	2	3
11/17/2008	2	3
11/24/2008	2	3
12/8/2008	2	3
12/15/2008	2	4
12/22/2008	2	4
1/5/2009	2	5
1/12/2009	2	3
1/19/2009	2	5
1/26/2009	2	6
2/9/2009	2	4
2/16/2009	2	5
2/23/2009	2	5
3/9/2009	2	6
3/16/2009	2	7
4/20/2009	2	7
5/11/2009	2	7
5/18/2009	2	8
6/8/2009	2	2
6/15/2009	2	2
6/22/2009	2	5
6/29/2009	2	6
7/13/2009	2	8
7/20/2009	2	7
7/27/2009	2	7
8/10/2009	2	6
8/17/2009	2	8
8/24/2009	2	7
8/31/2009	2	6
10/12/2009	2	9
10/19/2009	2	7
10/26/2009	2	3
11/9/2009	2	5
11/16/2009	2	5
11/23/2009	2	10
11/30/2009	2	5
7/14/2008	3	3
7/21/2008	3	2
7/28/2008	3	1
8/11/2008	3	2
8/18/2008	3	3
8/25/2008	3	1
9/1/2008	3	1
9/15/2008	3	2
9/21/2008	3	3
10/27/2008	3	2
11/10/2008	3	1
11/17/2008	3	1
11/24/2008	3	1
12/8/2008	3	0
12/15/2008	3	2
12/22/2008	3	1
1/5/2009	3	2
1/12/2009	3	1
1/19/2009	3	2
1/26/2009	3	1
2/9/2009	3	1
2/16/2009	3	2
2/23/2009	3	1
3/9/2009	3	1
3/16/2009	3	2
4/20/2009	3	1
5/11/2009	3	1
5/18/2009	3	1
6/8/2009	3	1
6/15/2009	3	1
6/22/2009	3	2
6/29/2009	3	1
7/13/2009	3	1
7/20/2009	3	1
7/27/2009	3	2
8/10/2009	3	2
8/17/2009	3	2
8/24/2009	3	2
8/31/2009	3	2
10/12/2009	3	3
10/19/2009	3	1
10/26/2009	3	2
11/9/2009	3	3
11/16/2009	3	1
11/23/2009	3	3
11/30/2009	3	2
7/14/2008	4	2
7/21/2008	4	3
7/28/2008	4	2
8/11/2008	4	0
8/18/2008	4	2
8/25/2008	4	2
9/1/2008	4	1
9/15/2008	4	1
9/21/2008	4	2
10/27/2008	4	0
11/10/2008	4	0
11/17/2008	4	1
11/24/2008	4	2
12/8/2008	4	1
12/15/2008	4	2
12/22/2008	4	1
1/5/2009	4	0
1/12/2009	4	1
1/19/2009	4	0
1/26/2009	4	2
2/9/2009	4	2
2/16/2009	4	2
2/23/2009	4	2
3/9/2009	4	1
3/16/2009	4	3
4/20/2009	4	5
5/11/2009	4	1
5/18/2009	4	2
6/8/2009	4	2
6/15/2009	4	5
6/22/2009	4	3
6/29/2009	4	2
7/13/2009	4	2
7/20/2009	4	1
7/27/2009	4	1
8/10/2009	4	4
8/17/2009	4	2
8/24/2009	4	2
8/31/2009	4	2
10/12/2009	4	4
10/19/2009	4	3
10/26/2009	4	3
11/9/2009	4	1
11/16/2009	4	1
11/23/2009	4	1
11/30/2009	4	1
7/14/2008	5	5
7/21/2008	5	1
7/28/2008	5	7
8/11/2008	5	2
8/18/2008	5	2
8/25/2008	5	3
9/1/2008	5	5
9/15/2008	5	5
9/21/2008	5	6
10/27/2008	5	5
11/10/2008	5	3
11/17/2008	5	4
11/24/2008	5	3
12/8/2008	5	3
12/15/2008	5	4
12/22/2008	5	2
1/5/2009	5	4
1/12/2009	5	2
1/19/2009	5	5
1/26/2009	5	3
2/9/2009	5	2
2/16/2009	5	3
2/23/2009	5	0
3/9/2009	5	3
3/16/2009	5	1
4/20/2009	5	4
5/11/2009	5	1
5/18/2009	5	3
6/8/2009	5	0
6/15/2009	5	3
6/22/2009	5	3
6/29/2009	5	3
7/13/2009	5	3
7/20/2009	5	3
7/27/2009	5	2
8/10/2009	5	1
8/17/2009	5	3
8/24/2009	5	3
8/31/2009	5	3
10/12/2009	5	2
10/19/2009	5	4
10/26/2009	5	3
11/9/2009	5	4
11/16/2009	5	3
11/23/2009	5	5
11/30/2009	5	3
7/14/2008	6	4
7/21/2008	6	5
7/28/2008	6	7
8/11/2008	6	7
8/18/2008	6	2
8/25/2008	6	4
9/1/2008	6	5
9/15/2008	6	5
9/21/2008	6	2
10/27/2008	6	3
11/10/2008	6	6
11/17/2008	6	5
11/24/2008	6	3
12/8/2008	6	4
12/15/2008	6	7
12/22/2008	6	4
1/5/2009	6	7
1/12/2009	6	2
1/19/2009	6	1
1/26/2009	6	4
2/9/2009	6	2
2/16/2009	6	1
2/23/2009	6	5
3/9/2009	6	5
3/16/2009	6	1
4/20/2009	6	3
5/11/2009	6	4
5/18/2009	6	4
6/8/2009	6	5
6/15/2009	6	2
6/22/2009	6	3
6/29/2009	6	2
7/13/2009	6	5
7/20/2009	6	5
7/27/2009	6	3
8/10/2009	6	3
8/17/2009	6	3
8/24/2009	6	4
8/31/2009	6	5
10/12/2009	6	3
10/19/2009	6	2
10/26/2009	6	2
11/9/2009	6	2
11/16/2009	6	3
11/23/2009	6	0
11/30/2009	6	2

I appreciate all the help

Thank you in advance

Kind regards,
Zak

GraH · Jun 17, 2022

if you are looking for the frequency

Book1

F

G

H

I

J

K

L

M

2

Bins *

1

2

3

4

5

6

3

1

2

17

18

7

10

5

3

8

6

5

19

9

6

4

10

3

-

2

6

8

7

5

10

9

-

2

6

10

8

6

7

6

-

1

9

7

3

7

-

1

4

10

8

1

3

-

11

9

-

1

-

12

10

-

1

-

13

+10

-

14

15

16

* there is actually a formula to define this, but I do not know that one by heart

17

18

Sheet1

Cell Formulas
Range	Formula
G2:L2	G2	=TRANSPOSE(UNIQUE(C3:C269))
G3:L13	G3	=FREQUENCY(IF($C$3:$C$269=G2,$D$3:$D$269),$F$3:$F$12)
Dynamic array formulas.

EDIT: @6StringJazzer posted the other thing you potentially meant with "frequency".

6StringJazzer · Jun 17, 2022

"Frequency" is a rate. Do you just want the total number of books each customer has bought?

I am showing the first part of your data and the formulas. No need to show all of your data again.

Or do you want the number of books each customer has bought each month? I would suggest a pivot table.

$scratch.xlsm

A

B

C

D

E

F

1

Date

Customer Number

Books sold

Customer Number

Books

2

7/14/2008

1

2

1

192

3

7/21/2008

1

5

2

198

4

7/28/2008

1

6

3

74

5

8/11/2008

1

5

4

83

6

8/18/2008

1

4

5

142

7

8/25/2008

1

3

6

166

8

9/1/2008

1

6

0

Sheet2

Cell Formulas
Range	Formula
E1:E8	E1	=UNIQUE(B:B)
F2:F7	F2	=SUMIF(B:B,E2,C:C)
Dynamic array formulas.

Zakarni · Jun 17, 2022

I am not looking for totals; I need how many times customer number 1 bought one book and two books,... so one. So, for example, in the sheet, I shared customer 1 bought one book three times.

Thank you all for the help

Zak

GraH · Jun 17, 2022

Add a zero to my bin range, before 1. It will work. My formula returns 4 because the bin is the upper boundary. So it was nclided the zero. But why do you have 0 books bought for a sales?

GraH · Jun 17, 2022

You can also use a pivot and count the instances or use countifs.

Frequency Function

Zakarni

New Member

Excel Facts

GraH

Well-known Member

6StringJazzer

Well-known Member

Zakarni

New Member

GraH

Well-known Member

GraH

Well-known Member

Similar threads

Forum statistics

Share this page

Frequency Function

Zakarni

New Member

Excel Facts

GraH

Well-known Member

6StringJazzer

Well-known Member

Zakarni

New Member

GraH

Well-known Member

GraH

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock