Countif or Sumif with frequency

[Paul]

Hello,

I am looking for a formula that can help me whit the next question:
In column C are dates and i want to know the frequency in Column B,
I inserted column B, because text is not handy. I want to now the
count/frequency and not the sum.

Example 1: I want to know for 16 january, the count of Column B (=3) and
afterwards i want in column D -1, so the result is 2 (in column D).

Example 2: 23 january, it are three rows, it contains number 3 twice, so i
want to know that the count/frequency is 2, and also -1, which result in 1
(in column D).

I hope that somebody can help me with this and give me a correct formula. I
spend a lot of time, but could not find the right formula.

Regards,

Paul

A B C D = Result
AB 1 1/9/09 0
AS 2 1/16/09 2
AB 1 1/16/09
DS 3 1/16/09
AB 1 1/23/09 1
DS 3 1/23/09
DS 3 1/23/09
AB 1 1/30/09 1
AS 2 1/30/09
AB 1 2/6/09 0
AB 1 2/13/09 0
AB 1 2/20/09 1
IT 4 2/20/09
AB 1 2/27/09 0
AB 1 3/6/09 0
AB 1 3/13/09 1
AS 2 3/13/09
AB 1 3/20/09 0
AS 2 3/27/09 1
AB 1 3/27/09

 

[Bernard Liengme]

Have a look at COUNTIF
best wishes

 

[Bernd P]

Hello Paul,

That's what I developed my UDF Pfreq for:
http://www.sulprobil.com/html/pfreq.html

You would not need your helper column B.

Just select an output range of 10 rows and 2 columns and array-enter:
=Pfreq(Pfreq(C1:C100,A1:A100))

Please note that you need to subtract 1 from resulting numbers...

Regards,
Bernd

 

[Paul]

Hey Bernd P,

Thank you for the help, the solution you gave, works fine.
It was new for me to add a UDF in VBA, but afterall it works,

Regards,

Paul

 

[Bernd P]

Hello Paul,

Thanks for your feedback. You are welcome.

Regards,
Bernd