Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency vs Count
Good Afternoon,
I need to count the frequency of a speficic number within a list of values. For Example: A b 1 Vanessa 1 2 David 0 3 David 0 4 Silas 0 5 Marly 1 6 Vanessa 0 8 David 0 9 Marly 1 10 Silas 0 11 Silas 1 12 Marly 1 13 Vanessa 1 So I need a formula that will count the frequency of 1 on column b for everytime column A equals Vanessa, David, Marly, Silas. Results expected: Vanessa 2 Marly 3 Silas 1 David 0 Thank you very much for your help. Vanessa Simmonds |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency vs Count
Assuming the unique names are listed in E1 down
In F1: =SUMPRODUCT((A$2:A$100=E1)*(B$2:B$100=1)) Copy down. Adapt ranges to suit your actual extents Success? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Vanessa Simmonds" wrote: I need to count the frequency of a specific number within a list of values. For Example: A b 1 Vanessa 1 2 David 0 3 David 0 4 Silas 0 5 Marly 1 6 Vanessa 0 8 David 0 9 Marly 1 10 Silas 0 11 Silas 1 12 Marly 1 13 Vanessa 1 So I need a formula that will count the frequency of 1 on column b for everytime column A equals Vanessa, David, Marly, Silas. Results expected: Vanessa 2 Marly 3 Silas 1 David 0 Thank you very much for your help. Vanessa Simmonds |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency vs Count
If the numbers are always either 1 or 0 as is shown in your sample data...
List the unique names in a range of cells. E1:E4 = Vanessa, David, Marly, Silas Enter this formula in F1 and copy down to F4: =SUMIF(A$1:A$12,E1,B$1:B$12) -- Biff Microsoft Excel MVP "Vanessa Simmonds" wrote in message ... Good Afternoon, I need to count the frequency of a speficic number within a list of values. For Example: A b 1 Vanessa 1 2 David 0 3 David 0 4 Silas 0 5 Marly 1 6 Vanessa 0 8 David 0 9 Marly 1 10 Silas 0 11 Silas 1 12 Marly 1 13 Vanessa 1 So I need a formula that will count the frequency of 1 on column b for everytime column A equals Vanessa, David, Marly, Silas. Results expected: Vanessa 2 Marly 3 Silas 1 David 0 Thank you very much for your help. Vanessa Simmonds |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency vs Count
Good Morning, All.
Thank you very much for your help. However I didn't explain myself correctly. The list of values on column B can also be 2 or "Absence" and I don't want excell to sum it. I want it to count the frequency of numeral values everytime column A is equal to Vanessa, David, Marly, Silas. A B 1 Vanessa 1 2 David Absence 3 David Absence 4 Silas 1 5 Marly 1 6 Vanessa Absence 8 David Absence 9 Marly 1 10 Silas 2 11 Silas 3 12 Marly 2 13 Vanessa 1 Vanessa 1 Marly 2 Silas 3 David 0 "T. Valko" wrote: If the numbers are always either 1 or 0 as is shown in your sample data... List the unique names in a range of cells. E1:E4 = Vanessa, David, Marly, Silas Enter this formula in F1 and copy down to F4: =SUMIF(A$1:A$12,E1,B$1:B$12) -- Biff Microsoft Excel MVP "Vanessa Simmonds" wrote in message ... Good Afternoon, I need to count the frequency of a speficic number within a list of values. For Example: A b 1 Vanessa 1 2 David 0 3 David 0 4 Silas 0 5 Marly 1 6 Vanessa 0 8 David 0 9 Marly 1 10 Silas 0 11 Silas 1 12 Marly 1 13 Vanessa 1 So I need a formula that will count the frequency of 1 on column b for everytime column A equals Vanessa, David, Marly, Silas. Results expected: Vanessa 2 Marly 3 Silas 1 David 0 Thank you very much for your help. Vanessa Simmonds |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency vs Count
Hello Vanessa,
Select a sufficiently long area with 2 columns and array-enter: =Pstat("count",B1:B999=1,A1:A999) Pstat is a UDF I wrote. You can find it he http://sulprobil.com/html/pstat.html Just press ALT + F11, insert a new (macro) module, copy my macro text into it and go back to your worksheet and use it like an other worksheet function. BTW: I would rather not do this with a SUMPRODUCT approach: http://sulprobil.com/html/sumproduct.html Regards, Bernd |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency vs Count
However I didn't explain myself correctly ..
Now, believe I caught your intents correctly the first time round, especially the COUNT and the list of variables in col B. But did you even try out the sumproduct expression suggested in my response? It returns the multi-criteria COUNT that you wanted, not "sum" as the function's name might have unwittingly misled. Give the earlier a shot, do not dismiss it just like that. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Vanessa Simmonds" wrote: Good Morning, All. Thank you very much for your help. However I didn't explain myself correctly. The list of values on column B can also be 2 or "Absence" and I don't want excell to sum it. I want it to count the frequency of numeral values everytime column A is equal to Vanessa, David, Marly, Silas. A B 1 Vanessa 1 2 David Absence 3 David Absence 4 Silas 1 5 Marly 1 6 Vanessa Absence 8 David Absence 9 Marly 1 10 Silas 2 11 Silas 3 12 Marly 2 13 Vanessa 1 Vanessa 1 Marly 2 Silas 3 David 0 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency vs Count
Good Morning, Max.
I did try "sumproduct" but it is not giving me the results I want. For example: A B 1 Vanessa 1 2 David Absence 3 David Absence 4 Silas 1 5 Marly 1 6 Vanessa Absence 8 David Absence 9 Marly 1 10 Silas 2 11 Silas 3 12 Marly 2 13 Vanessa 1 Sumproduct will return: Vanessa 2 David 0 Silas 1 Marly 2 As it is counting the numbers everytime column b = 1 and column A equal Vanessa, David, Silas, Marly. But I want to return the number of different numerical values in column B everytime column A is equal Vanessa, David, Silas or Marly. Vanessa 1 David 0 Silas 3 Marly 2 "Max" wrote: However I didn't explain myself correctly .. Now, believe I caught your intents correctly the first time round, especially the COUNT and the list of variables in col B. But did you even try out the sumproduct expression suggested in my response? It returns the multi-criteria COUNT that you wanted, not "sum" as the function's name might have unwittingly misled. Give the earlier a shot, do not dismiss it just like that. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Vanessa Simmonds" wrote: Good Morning, All. Thank you very much for your help. However I didn't explain myself correctly. The list of values on column B can also be 2 or "Absence" and I don't want excell to sum it. I want it to count the frequency of numeral values everytime column A is equal to Vanessa, David, Marly, Silas. A B 1 Vanessa 1 2 David Absence 3 David Absence 4 Silas 1 5 Marly 1 6 Vanessa Absence 8 David Absence 9 Marly 1 10 Silas 2 11 Silas 3 12 Marly 2 13 Vanessa 1 Vanessa 1 Marly 2 Silas 3 David 0 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency vs Count
Thanks for returning here, and for clarifying further/better
Ok, here's my revised venture for you .. With your source data as posted running in A1:B1 down In C1: =IF(SUMPRODUCT(($A$1:A1=A1)*($B$1:B1=B1)*ISNUMBER( B1))=1,A1,"") Copy down. Col C extracts the names from col A which satisfy your clarified criteria. Then assuming that unique names are listed in E1 down, viz.: Vanessa, David, etc In F1, copied down: =COUNTIF(C:C,E1) should now return the required results Success this time round? Celebrate it, hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Vanessa Simmonds" wrote: Good Morning, Max. I did try "sumproduct" but it is not giving me the results I want. For example: A B 1 Vanessa 1 2 David Absence 3 David Absence 4 Silas 1 5 Marly 1 6 Vanessa Absence 8 David Absence 9 Marly 1 10 Silas 2 11 Silas 3 12 Marly 2 13 Vanessa 1 Sumproduct will return: Vanessa 2 David 0 Silas 1 Marly 2 As it is counting the numbers everytime column b = 1 and column A equal Vanessa, David, Silas, Marly. But I want to return the number of different numerical values in column B everytime column A is equal Vanessa, David, Silas or Marly. Vanessa 1 David 0 Silas 3 Marly 2 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency vs Count
Hello,
Do you really want to use SUMPRODUCT, being forced to manually maintain a list of unique names? IMHO SUMPRODUCT has become the biggest time-waster for all Excel newbies who managed to understand its functionality: http://sulprobil.com/html/sumproduct.html I suggest to select an area of some rows and two columns and to array- enter: =Pstat("count",ISNUMBER(INDEX(Pfreq(A1:A13,B1:B13) ,,2)),Pfreq (A1:A13,B1:B13)) [Of course you might want to use a temporary area where you evaluate Pfreq() only once if you have thousand rows.] Pfreq you can get he http://sulprobil.com/html/pfreq.html Pstat is he http://sulprobil.com/html/pstat.html Regards, Bernd |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency vs Count
Hi Bernad,
I tried to use the formula below, however it is not working. =Pstat("count",ISNUMBER(INDEX(Pfreq(A1:A12,B1:B12) ,2)),Pfreq(A1:A12,B1:B12)) The result I get is #NAME?. Best Regards, Vanessa "Bernd P" wrote: Hello, Do you really want to use SUMPRODUCT, being forced to manually maintain a list of unique names? IMHO SUMPRODUCT has become the biggest time-waster for all Excel newbies who managed to understand its functionality: http://sulprobil.com/html/sumproduct.html I suggest to select an area of some rows and two columns and to array- enter: =Pstat("count",ISNUMBER(INDEX(Pfreq(A1:A13,B1:B13) ,,2)),Pfreq (A1:A13,B1:B13)) [Of course you might want to use a temporary area where you evaluate Pfreq() only once if you have thousand rows.] Pfreq you can get he http://sulprobil.com/html/pfreq.html Pstat is he http://sulprobil.com/html/pstat.html Regards, Bernd |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency vs Count
Good Morning, Max.
The formula does work. Thank you very much for your help. My only problem now is the time it takes excel to calculate it. My spreadsheet has about 40,000 rows so it is taking a long time to update it. I will carry on looking for a different solution but for the moment I am happy with the formula you gave me. Thank you very much for your help once again. "Max" wrote: Thanks for returning here, and for clarifying further/better Ok, here's my revised venture for you .. With your source data as posted running in A1:B1 down In C1: =IF(SUMPRODUCT(($A$1:A1=A1)*($B$1:B1=B1)*ISNUMBER( B1))=1,A1,"") Copy down. Col C extracts the names from col A which satisfy your clarified criteria. Then assuming that unique names are listed in E1 down, viz.: Vanessa, David, etc In F1, copied down: =COUNTIF(C:C,E1) should now return the required results Success this time round? Celebrate it, hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Vanessa Simmonds" wrote: Good Morning, Max. I did try "sumproduct" but it is not giving me the results I want. For example: A B 1 Vanessa 1 2 David Absence 3 David Absence 4 Silas 1 5 Marly 1 6 Vanessa Absence 8 David Absence 9 Marly 1 10 Silas 2 11 Silas 3 12 Marly 2 13 Vanessa 1 Sumproduct will return: Vanessa 2 David 0 Silas 1 Marly 2 As it is counting the numbers everytime column b = 1 and column A equal Vanessa, David, Silas, Marly. But I want to return the number of different numerical values in column B everytime column A is equal Vanessa, David, Silas or Marly. Vanessa 1 David 0 Silas 3 Marly 2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to count the frequency of each word in Excel | Excel Discussion (Misc queries) | |||
??? FREQUENCY/COUNT | Excel Discussion (Misc queries) | |||
count frequency of two values in same row | Excel Worksheet Functions | |||
Count the frequency of all numbers in a column | New Users to Excel | |||
Using FREQUENCY Function to Count | Excel Worksheet Functions |