Forum Discussion
Last 12 months counting
Thanks again,
Alexis
What is your definition of a "rolling 12 month count"?
What I have in mind with a rolling 12 months would be a far simpler formula than the one you have.
=SUM(Sheet1!D3:O3)All it does is get the 12 months up to and including the most recent. And that formula just can be copied across. But you can't begin doing that until the 12th month of any given series. (i.e., you can't do a rolling 12 months in the 10th month...)
- Well the problem is that one row can only be counted once in the last 12 months. Eg sheet 2 n2 is counted as it was the first time for 12 months that something other than 0 appeared. That's fine and works for most of what I need.
Row 2 is an exception. Sheet 2 A3 is counted- that's fine. I don't want to count December or Jan because it is within 12 months of Jan22. Again, that's fine. But Dec22 and Jan23 are stopping Feb 23 being counted. Feb 23 should be the 2nd time it's counted because it is 12 months on from the first count in Jan22. Does that make sense?I'm not following your description. Maybe it's the "trees" vs the "forest" kind of issue. I'm looking for the higher level definition. To me a rolling twelve months would not care whether there was a zero in any one of those twelve months. That would be that month's value. Period.
You seem to be saying something along the lines of "I only want 12 months of values other than zero," but that wouldn't be a "rolling twelve months" (in my dictionary); that would be labeled something else.
So what we're looking for, in order to write a formula, is a plain english definition of what YOU mean by what you've been referring to as "rolling twelve months" [but perhaps needs a more accurate or precise label].
- OK, sorry if it's not clear.
Each row represents a person. I only want to count each person once in any 12 month period. If they were counted in Jan22, they can't be counted again until the following Jan. I have a problem in row 2 whereby they are counted once in Jan 22 but not again (by using my formula). By using your formula, you are counting them every time they appear. I need a unique count for the last 12 months.