Forum Discussion
Last 12 months counting
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...)
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.I've been taking your very first sentence:
I am trying to produce a rolling 12 month count sheet
to mean that you want a rolling 12 month total (which is how that term is usually used).
So I'm still not really clear. What is being counted? The appearance of a number other than zero in any of the 12 months? So regardless of what the number is, it's either one or zero as far as the 12 month count is concerned? Or is it the total of the numbers in a contiguous 12 month period for each row. Or something else.
The word "unique" is ambiguous in this context. Are saying you're looking, in effect, for "did this person appear once in the preceding 12 months"? So maybe what should be appearing is a simple "Yes" or "No" referring to the previous 12 month period(?) Whether or not that's the case, you seem to want a binary outcome, 1 or 0 OR Yes or No.