Countif or Sumif with frequency
 

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

Countif or Sumif with frequency
 

Have a look at COUNTIF
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Paul" wrote in message
...
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

Countif or Sumif with frequency
 

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

Countif or Sumif with frequency
 

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" wrote:

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

Countif or Sumif with frequency
 

Hello Paul,

Thanks for your feedback. You are welcome.

Regards,
Bernd