Frequency vs Count

[Vanessa Simmonds]

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

 

[Max]

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

 

[T. Valko]

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)

 

[Vanessa Simmonds]

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

 

[Bernd P]

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 here:
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

 

[Max]

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]

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]

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

 

[Bernd P]

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 here:
http://sulprobil.com/html/pfreq.html

Pstat is here:
http://sulprobil.com/html/pstat.html

Regards,
Bernd

 

[Vanessa Simmonds]

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

 

[Vanessa Simmonds]

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.