Previous years registered: Excel COUNTIF and RETURN for X years

0
votes

UPDATED screenshot after attempting @Dude_Scott's suggestion:

Desired output of data is in the blue table.

Our data includes users who have registered between 1989-2016. All have registered at least once. Some register every year and some skip years. Our question is for each year, find how many years previous users registered. We want results to be 0yr, 1yr, 2yr, 3yr, etc., for each year. Arrays are working correctly.

I've organized the data structure in Excel this way:

1) UserID All Years

For year 1989, result is 0, since it was the first year of data collection.

For year 1990, this formula returns the expected count:

=COUNT(IF($B$2:$B$11613=1989,1/COUNTIFS($A$2:$A$11613,$A$2:$A$11613,$B$2:$B$11613,1989)))

Beginning with year 1991 is where I am tripped up: I can't find for multiple years. This formula is not working:

=COUNT(IF(AND(OR($B$2:$B$11613=1989,1990,1/COUNTIFS($A$2:$A$11613,$A$2:$A$11613,$B$2:$B$11613,1989,1990)))))

Where do I argue "COUNTIF 0 yr, 1 yr, 2 yr", etc. Thanks in advance. --f66

2
arraysexcelexcel-formulacountif
Have you tried writing a VBA function to do this? – KolaB
This question is well suited to Python. If you uploaded your file I will show you a solution – Worm
@KolaB, no. I've tried SQL and Access. – fidget66
@worm, working on how to figure out how to upload file...stay tuned. – fidget66
If the registration year doesn't have duplicates per User ID, what's wrong with just COUNTIFS()? – BruceWayne

2 Answers

0
votes

Since your using COUNTIF, I'm assuming you can use SUMPRODUCT also. In the below screen shot I am using this formula 

=SUMPRODUCT(($A$2:$A$11=$D3)*($B$2:$B$11<=F$2))&" yr"

Without a sample of the workbook its a little difficult to determine what the output of your data should look like, but give it a go.

Side note, I would suggest updating the User ID and Years into a table format and giving it a named range, so you don't iterate though tens of thousands of lines with the array formula.

enter image description here

0
votes

I may be reading this all wrong, but this seems to be a straightforward use case for a pivot table!

  1. Select your data range
  2. Set USERID as your row headings
  3. Set YEARS as your column headings
  4. Set count of USERID as your values

I do not use excel anymore, but below is a link to output for doing this with test data on google sheets.

Google sheets pivot table for dummy data