Counting summaries

[j_sun]

Hi All,

Tried searching existing forums with no luck.


I've got a long list of every instance that a customer made a change to their account. I'm trying to create a summary showing how many people made n number of changes in a given year.


For example:

[pre]

CHANGES IN YEAR    COUNT
0                  253
1                  8596
2                  1258
3                  874
I can get a pivot table to show me how many changes each person made.

For example:

NAME          CHANGES
Joe Smith     2
Mary Smith    1
Joan Doe      2

[/pre]
My data source is a simple two column list with NAME and DATE OF CHANGE.


How do I get the table to display the summary I want?


Thanks in advance,

Jason

 

[Luke M]

Can we just build some formulas based off the PivotTable you have? Assuming PT is in column A:B, you could then build a small table in col D that looks like

[pre]

CHANGES IN YEAR    COUNT
0                  =COUNTIF(B:B,D2)
1                  =COUNTIF(B:B,D3)
2                  =COUNTIF(B:B,D4)
3                  =COUNTIF(B:B,D5)

[/pre]

 

[j_sun]

That works!

I think you can start charging me tutoring fees Luke as much as you help me.

 

[SirJB7]

Hi, j_sun!


Perhaps you want to give a look at this file, an approach with helper columns and without pivot tables.:

https://dl.dropbox.com/u/60558749/Counting%20summaries%20%28for%20j_sun%20at%20chandoo.org%29.xlsx


Regards!


@Luke M


Hi!


Considering your CASFFML lemma...


Column A (Aux 1): =SI(CONTAR.SI(C$2:C2;C2)=1;1;0) ----> in english: =IF(COUNTIF(C$2:C2,C2)=1,1,0)

Column B (Aux 2): =SUMA(A$2:A2) -----> in english: =SUM(A$2:A2)

Column C (NAME): NAME list

Column D (DATE OF CHANGE): DATE OF CHANGE list

Column E (Unique NAME): =BUSCARV(FILA()-1;B:C;2;FALSO) -----> in english: =VLOOKUP(ROW()-1,B:C,2,FALSE)

Column F (Count by NAME): =CONTAR.SI(C:C;E2) -----> in english: =COUNTIF(C:C,E2)


Regards!

 

[SirJB7]

Hi, j_sun!

Please download again from same link. Missed last summary. I apologize.

Regards!


@Luke M

Hi!

Column G (No. of changes): =FILA()-1 -----> in english: =ROW()-1

Column H (Count of times): =CONTAR.SI(F:F;G2) -----> in english: =COUNTIF(F:F,G2)

Regards!


@All

BTW, I think you wouldn't be able to find out whose customers didn't make any change, so first formula line in both explanation and solution (0 in count of changes) will always retrieve zero. Hope to not being wrong, but if data is about changing dates...

Regards!

 

[Luke M]

No worries j_sun! Maybe I should see if there's someway to add a PayPal button to my account page (ha ha).