Last 12 months counting

[Alexis_Piper]

I am trying to produce a rolling 12 month count sheet that I have attached. In the large part, it works, but as explained in the workbook, sometimes when there is a cell with data that is not counted, because it's counted in a previous month, it stops a count from working later in the year. 

In the doc, Jan 22 is counted on sheet 2 in both cases. Great. Feb 23 is counted in row 2 because there was nothing between those dates. Great. 

The problem is in row 3. Jan 22 is counted. Dec 22 is therefore discounted in Jan 23. I want Feb 23 to return 2 but as Dec 22 and Jan 23 are not 0 it is not working. Can anyone see a way around this?

Any help would be greatly appreciated.

rolling test.xlsx

[#2]

@Alexis_Piper 

That link doesn't work for folks who aren't part of that particular sharepoint account. So you need to post a copy on OneDrive or GoogleDrive, pasting a link that actually grants access. Or a different category of link that grants access to your Sharepoint area.

[#3]

Thank you Mathetes- I have changed the link- would you mind checking that you can now see it?

Thanks again,

Alexis

[#4]

@Alexis_Piper 

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...) 

[#5]

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?

[#6]

@Alexis_Piper 

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]. 

[#7]

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.

[#8]

@Alexis_Piper 

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. 

[#9]

Ah I see. I understand the confusion now- your summary is exactly it. I want a binary- yes I am counting you this month or not. The rolling 12 month was referring to always being the previous 12 months. Thanks for bearing with

[#10]

@Alexis_Piper 

There's still some ambiguity, though. (Sorry: this is why I was looking for a clear definition from the very start; and, yes, I can be very finicky about what I mean by clear definitions).

Do you want to know the number of months that a person appeared during the preceding 12 months, or only that they appeared at least once during the preceding 12 months.

In other words, is it

• binary for 12 months, so your 12 month summary will be either 1 or zero, Yes or No; ,

or

• binary for each of the preceding 12 months, so the rolling 12 months could be any number up to 12.

[#11]

Hello,

No problem. I have been there too before asking similar questions.
I would like binary for 12 months- just have they appeared in the last 12 months or not.
If you could make that work that would be amazing!

[#12]

@Alexis_Piper 

Does this do it?

=IF(SUM(Sheet1!C2:N2)>0,"Yes","")

Copied forward; the relative references change to always be dealing with the "most recent twelve months"