Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif or Sumif with frequency
Hello Paul,
Thanks for your feedback. You are welcome. Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif/countif | Excel Discussion (Misc queries) | |||
sumif or countif not sure | Excel Worksheet Functions | |||
CountIF or SumIF | Excel Discussion (Misc queries) | |||
Countif - Buying Frequency | Excel Worksheet Functions | |||
COUNTIF or SUMIF or ?? | Excel Worksheet Functions |