Counting / Criteria / Date Question

[alaskanpilot]

Hi all,

This may be a simple one but I'm stuck on it right now.

I have a simple spreadsheet. Down column A, Are a bunch of dates. There may be repeats.

In column B, corresponding to the dates, is a list of company names. There may be repeats here too.

I want to find a way to determine if any company name appears in Column B 3 times within any 30-day period, in reference to those dates in Column A.

I'm guessing some variant of COUNTIF or COUNTIFS would work, possibly putting such a formula into Column C each time a new date/company is entered, to run the check. I'm just having trouble stringing it all together.

Any ideas? Thanks!

 

[jc.021286]

Hey there,
Are you looking at a trailing 30 day? or 30 days as any month
Thus, trailing 30 day would flag
Jan 14 - Company A
Jan 15 - Company A
Feb 2 - Company A
Feb 2 - Company A

Trailing 30 day would flag the above, while a 30 day month comparison would not. Which would it be?

I'm thinking using a sumproduct formula would work very well for you. Or using a pivot table with a count function.

Let me know,
jc

 

[alaskanpilot]

thanks for the reply. Right now, I'm looking at *any* 30 day period for any given company, so in the example you provided, then yes... i would want that to flag. So it sounds like I need the trailing 30 day window.

 

[jc.021286]

Then try,
in column C this formula

=SUMPRODUCT(--($A$1:$A$100<=A1),--($A$1:$A$100>=(A1-30)),--($B$1:$B$100=B1))

What I'm doing is using the (--) to get my true false array to 1=True, 0=False
so I'm going to see set all dates that are less than and equal to my date, = 1, I'm going to multiply this by an array where the dates that are greater than or equal to my date less 30 days will be equal to 1

the zeroes will cancel out any dates that do not matter and I will have essentially a 1 beside all dates that are withing 30 days trailing my date in A1.
Now I'll multiply that by a matrix where the companies are equal to my company and I'll get my answer as to how many companies within the past 30 days are associated with that date range and company.

if you had only 1 value, then you ought to get a 1. However, if you have 3 that qualify, the date of the last one will flag and catch the previous 3.

Thus in my example
Jan 14 - Company A = 1
Jan 15 - Company A = 2
Feb 2 - Company A = 4
Feb 2 - Company A = 4

For the other methodology of it being in 1 month, I'd use the EOMONTH function to get all the dates to their respective end dates of that month for the same formula to work easily.

let me know if you have any questions or want a tweak of any kind,
jc

 

[alaskanpilot]

Wow..

That seems to be doing the trick nicely! I'm going to spend some time trying to understand what you've done here, in particular the "--" operator. If I am understanding this right...

With this formula, through the -- operator, are you basically assigning a 1 or a 0 to each cell value within the ranges involved, based on the criteria specified? Is it correct that the sumproduct then, is summing up the results of numerous calculations, each of which is a multiplication of 3 digits that are either 0 or 1? I think I'm starting to make some sense of this. I can see how the 0's disqualify a particular entry from being summed up.

Thank you very much, not just for the formula but for explaining it as well. It seems more and more that Excel is more about creative approaches than simply knowing some formula keywords. I'll study this some more and add it to my bag of tricks. THANKS!

 

[jc.021286]

Very Good, Looks like you nailed it,

If I had only ($A$1:$A$100=A1) I'd get 100 {True, False,False,False...} having the (--) at the beginning turns it into 1s and 0s. Another approach is just multiplying by 1
--($A$1:$A$100=A1) is the same as 1*($A$1:$A$100=A1)

In Excel, you should see an audit tool called Evaluate Formula. Looks like a magnifying glass looking at an "Fx"
In there you can go step by step through the logic of the formula. An amazing tool if you have very complex formulas that you are trying to decipher.

jc