1

I'm looking to create a formula that counts the number of sign ups for a given day. My input data is based off an event log, and looks something like this:

AccountName | SignUpDay | EventLogged

acct1 | 06/01/2016 | 06/01/2016

acct1 | 06/01/2016 | 06/05/2016

acct2 | 06/01/2016 | 06/02/2016

acct3 | 06/01/2016 | 06/04/2016

acct3 | 06/01/2016 | 06/06/2016

acct4 | 06/03/2016 | 06/06/2016



The above is dummy data. But lets say I have 10k lines for my input data. For my output, given a specific day I want to look at the input data and return the number of signups for that particular day. What I want to achieve is something like this:

SignUpDay | Count

06/01/2016 | 3

06/02/2016 | 0

06/03/2016 | 1

I know I could probably do something like this in R, but I'm working within what I have right now, which is excel. Anyone have any ideas on how to achieve this?

Gshock
  • 317
  • 1
  • 4
  • 13

2 Answers2

1

With Excel's FREQUENCY() and an Array formula:

Assuming

  • your sheet is set up with AccountNames in A2:A100
  • your SignupDates are stored in B2:B100
  • the current Date you want to subset for is in F2

then enter: =SUM(IF(FREQUENCY(MATCH(IF(B$2:B$100=F2,A$2:A$100,""),IF(B$2:B$100=F2,A$2:A$100,""),0),MATCH(IF(B$2:B$100=F2,A$2:A$100,""),IF(B$2:B$100=F2,A$2:A$100,""),0))>0,1))-1

and press CTRL+SHIFT+ENTER to enter this as an array formula.

enter image description here

Ulli Schmid
  • 1,167
  • 1
  • 8
  • 16
1

With your list of sequential dates in, for example, F2:Fn, you may try this array-entered formula:

G2:  =SUM(1/COUNTIF(AccountName,AccountName)*(SignUpDay=F2))

AccountName and SignUpDay should refer only to the existing data range (no blanks). If there are blanks, a more complex formula would be required. If you use a table, with structured addressing, the names can adjust automatically.

Also, the formula assumes that the same account will not sign up on more than one day. If that might be the case, a more complex formula would be required.

eg:

=SUM(1/COUNTIF(SignUpTable[[AccountName ]],SignUpTable[[AccountName ]])*(SignUpTable[ [ SignUpDay ] ]=F2))

To array-enter a formula, after entering the formula into the cell or formula bar, hold down while hitting . If you did this correctly, Excel will place braces {...} around the formula.

enter image description here

EDIT: If your data does not fit into the above constraints, I would suggest one of the solutions linked to by @pnuts, in his comments, where you add an additional column to your data with the formula:

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

and then construct a Pivot table with SignUpDay in the Rows area, and this new column in the Values area. A disadvantage of the Pivot Table solution is that dates with zero signups will not be represented in the table.

In Excel 2013+ it is possible to generate Unique Counts in the Values area, but you mentioned you are using Excel 2010, so that is not a possibility.

Community
  • 1
  • 1
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • agreed, my formula pulled all the values including duplicates. thanks! – cyboashu Aug 10 '16 at 22:57
  • @RonRosenfeld This doesn't work - enter ("acct3","06/03/2016","asdf") as an additional entry – Ulli Schmid Aug 11 '16 at 09:25
  • It's because Sum() adds up fractionals which only sum up to the correct count if you don't filter any values out – Ulli Schmid Aug 11 '16 at 09:26
  • @UlliSchmid You have posed an example where the same account signs up on two different days. – Ron Rosenfeld Aug 11 '16 at 10:21
  • I guess you're right, for this specific application the formula is correct. It just won't work for the general case, if you want to subset by some criterion that is independent from the "unique" key. – Ulli Schmid Aug 11 '16 at 10:30