I think you can do this with a formula if you are OK with counting unique values by date, meaning a value will be counted once in every month it appears. This will handle duplicate values and doesn’t require sorting.
Enter the following as an array formula - you have to type Ctrl-Shift-Enter. In this example, the month you are matching is in cell D20.
If you want to count a value only once when it appears in multiple months, I think you’re screwed. That wouldn’t be easy even in SQL (although at least it would be possible and I don’t think it is in Excel).
Troutman, you are correct that if a value (these are employee ID numbers) has a ‘1’ in multiple months, I want to count her for each month. I think that’s what you asked.
I suspected I wanted to use the FREQUENCY function but I couldn’t figure out how. I can’t work on it right now but I will try later today or tomorrow and see if your solution works. Thanks!
penultima - I’ll take a look at your solution also. Thank you.
Is the problem with the solution I provided that you can’t sort the data or that the data in ColC won’t always be just 1 or 0? If the latter, I’d suggest you just add a column with the formula =IF(C <> 0, 1, 0), then use the new column in place of ColC.
If the issue is sorting, you can always create a ghost table to do the operations on.
No, csmodes, the problem is that the values in column C (0 or 1) are not constant, so I would have to be constantly resorting data every time the user input changed. The table already has 26 columns and 10,000 rows and growing so I’m afraid constant re-sorting would be too slow, if I could even do it.
I just created a table with over 40,000 rows and 30 columns and it sorted in less than a second. I agree constant sorting isn’t ideal, though. If the data is accessed and updated frequently, sorting is a bad option.