Counting distinct occurences between two columns based on date

[SHUTTEHFACE]

Hi All,

I'm using the below formula to try and calculate count of dates that are older than 90 days old between two columns (AK and AM). I want to make sure that should the value in AK AND the value in AM both be older than 90 days, they are only counted once.

<code>

=SUMPRODUCT(--('sheet1'!$B:$B="THC"),--('sheet1'!$AK:$AK<=TODAY()-90),--('sheet1'!$AM:$AM<=TODAY()-90),--('sheet1'!$E:$E={"1, THC Pilot","1, Both Pilot","2, THC Pilot","2, Both Pilot","3, THC Pilot","3, Both Pilot"}))

</code>
Can anyone assist?

Cheers,

STF

 

[SHUTTEHFACE]

Any ideas?

I'm getting a #value ! error with the current formula.

I'm trying to count all the dates in column AM and AK that are older than 90 days and also have "THC" marked in column B and one of {"1, THC Pilot","1, Both Pilot","2, THC Pilot","2, Both Pilot","3, THC Pilot","3, Both Pilot"} in column E.

thanks!

 

[Peter_SSs]

Any ideas?

I would strongly recommend not using whole column references in a formula like this. You are asking for a very large number of calculations.

In my suggestion below, I have only used down to row 15 for testing, but instead choose a number big enough to cover the largest data you might have but not too huge. (eg 5000?)

Try a formula like this

=SUMPRODUCT(--(B2:B15="THC"),--(AK2:AK15<=TODAY()-90),--(AM2:AM15<=TODAY()-90),--ISNUMBER(SEARCH("|"&E2:E15&"|","|1, THC Pilot|1, Both Pilot|2, THC Pilot|2, Both Pilot|3, THC Pilot|3, Both Pilot|")))

 

[SHUTTEHFACE]

I would strongly recommend not using whole column references in a formula like this. You are asking for a very large number of calculations.

In my suggestion below, I have only used down to row 15 for testing, but instead choose a number big enough to cover the largest data you might have but not too huge. (eg 5000?)

Try a formula like this

=SUMPRODUCT(--(B2:B15="THC"),--(AK2:AK15<=TODAY()-90),--(AM2:AM15<=TODAY()-90),--ISNUMBER(SEARCH("|"&E2:E15&"|","|1, THC Pilot|1, Both Pilot|2, THC Pilot|2, Both Pilot|3, THC Pilot|3, Both Pilot|")))

Thanks! Would you mind explaining the last piece of the formula? I'm not quite following.

Cheers.

 

[Peter_SSs]

Thanks! Would you mind explaining the last piece of the formula? I'm not quite following.

I assume that you mean the blue part below.

=SUMPRODUCT(--(B2:B15="THC"),--(AK2:AK15<=TODAY()-90),--(AM2:AM15<=TODAY()-90),--ISNUMBER(SEARCH("|"&E2:E15&"|","|1, THC Pilot|1, Both Pilot|2, THC Pilot|2, Both Pilot|3, THC Pilot|3, Both Pilot|")))

It checks your extra requirement:

.. one of {"1, THC Pilot","1, Both Pilot","2, THC Pilot","2, Both Pilot","3, THC Pilot","3, Both Pilot"} in column E

It does so by joining all of those values in one string, delimited by the "|" (any character not in the list of values) so we get a single string:
"|1, THC Pilot|1, Both Pilot|2, THC Pilot|2, Both Pilot|3, THC Pilot|3, Both Pilot|"
The formula then puts the "|" character before and after each value in column E and checks to see if it can find those values in the string.
For example,
- if E2 is "2, THC Pilot" the formula would look (SEARCH) for "|2, THC Pilot|" in the red string above. That string is found starting at position 28 in the red string so the SEARCH function returns 28. So ISNUMBER is true so row 2 would be included in the count.
- if E3 is "abc" the formula would look (SEARCH) for "|abc|" in the red string above. It is not found so SEARCH returns an error so ISNUMBER is false so row 3 would not be included in the count.

Hope that clarifies for you (it's usually easier to write the formulas than explain them ).

 

[SHUTTEHFACE]

I assume that you mean the blue part below.

=SUMPRODUCT(--(B2:B15="THC"),--(AK2:AK15<=TODAY()-90),--(AM2:AM15<=TODAY()-90),--ISNUMBER(SEARCH("|"&E2:E15&"|","|1, THC Pilot|1, Both Pilot|2, THC Pilot|2, Both Pilot|3, THC Pilot|3, Both Pilot|")))

It checks your extra requirement:
It does so by joining all of those values in one string, delimited by the "|" (any character not in the list of values) so we get a single string:
"|1, THC Pilot|1, Both Pilot|2, THC Pilot|2, Both Pilot|3, THC Pilot|3, Both Pilot|"
The formula then puts the "|" character before and after each value in column E and checks to see if it can find those values in the string.
For example,
- if E2 is "2, THC Pilot" the formula would look (SEARCH) for "|2, THC Pilot|" in the red string above. That string is found starting at position 28 in the red string so the SEARCH function returns 28. So ISNUMBER is true so row 2 would be included in the count.
- if E3 is "abc" the formula would look (SEARCH) for "|abc|" in the red string above. It is not found so SEARCH returns an error so ISNUMBER is false so row 3 would not be included in the count.

Hope that clarifies for you (it's usually easier to write the formulas than explain them ).

Thanks! I don't think the formula is quite doing what I want it to.

Here is what the output should return:

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2121;width:44pt" width="58"> <col style="width:48pt" width="64"> </colgroup><tbody>
[TD="class: xl63, width: 64, align: center"]
[/TD]
[TD="class: xl64, width: 58, align: center"] Data more than 90 days old
[/TD]
[TD="class: xl64, width: 64, align: center"] Data less than 90 days old
[/TD]

[TD="class: xl65, align: center"]THC pilot[/TD]
[TD="class: xl66, align: center"]7[/TD]
[TD="class: xl66, align: center"]5
[/TD]

</tbody>

Data Sample

<colgroup><col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> <col style="mso-width-source:userset;mso-width-alt:2998;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:2486; width:51pt" width="68" span="2"> </colgroup><tbody>
[TD="class: xl65, width: 59, align: center"] group
[/TD]
[TD="class: xl65, width: 82, align: center"] Priority
[/TD]
[TD="class: xl66, width: 68, align: center"] Received
[/TD]
[TD="class: xl66, width: 68, align: center"] Final approva l
[/TD]

[TD="align: center"]THC[/TD]
[TD="align: center"]3, THC Pilot[/TD]
[TD="class: xl67, align: center"]4/17/2018[/TD]
[TD="class: xl67, align: center"]8/8/2018[/TD]

[TD="align: center"]THC[/TD]
[TD="align: center"]1, THC Pilot[/TD]
[TD="class: xl67, align: center"]8/7/2017[/TD]
[TD="class: xl67, align: center"]8/8/2018[/TD]

[TD="align: center"]THC[/TD]
[TD="align: center"]2, Both Pilot[/TD]
[TD="class: xl67, align: center"][/TD]
[TD="class: xl67, align: center"][/TD]

[TD="align: center"]THC[/TD]
[TD="align: center"]1, Both Pilot[/TD]
[TD="class: xl67, align: center"]9/25/2017[/TD]
[TD="class: xl67, align: center"]8/8/2018[/TD]

[TD="align: center"]THC[/TD]
[TD="align: center"]1, Both Pilot[/TD]
[TD="class: xl67, align: center"]8/15/2017[/TD]
[TD="class: xl67, align: center"]8/8/2018[/TD]

[TD="align: center"]THC[/TD]
[TD="align: center"]3, Both Pilot[/TD]
[TD="class: xl67, align: center"]9/18/2017[/TD]
[TD="class: xl67, align: center"][/TD]

[TD="align: center"]THC[/TD]
[TD="align: center"]1, Both Pilot[/TD]
[TD="class: xl67, align: center"]8/15/2017[/TD]
[TD="class: xl67, align: center"][/TD]

[TD="align: center"]THC[/TD]
[TD="align: center"]1, THC Pilot[/TD]
[TD="class: xl67, align: center"]7/6/2017[/TD]
[TD="class: xl67, align: center"][/TD]

[TD="align: center"]THC[/TD]
[TD="align: center"]1, THC Pilot[/TD]
[TD="class: xl67, align: center"]8/28/2017[/TD]
[TD="class: xl67, align: center"]3/20/2018[/TD]

[TD="align: center"]THC[/TD]
[TD="align: center"]1, Both Pilot[/TD]
[TD="class: xl67, align: center"]8/15/2017[/TD]
[TD="class: xl67, align: center"][/TD]

[TD="align: center"]THC[/TD]
[TD="align: center"]1, Both Pilot[/TD]
[TD="class: xl67, align: center"]7/11/2017[/TD]
[TD="class: xl67, align: center"]1/20/2018[/TD]

[TD="align: center"]THC[/TD]
[TD="align: center"]1, Both Pilot[/TD]
[TD="class: xl67, align: center"]9/25/2017[/TD]
[TD="class: xl67, align: center"][/TD]

[TD="align: center"]THC[/TD]
[TD="align: center"]2, Both Pilot[/TD]
[TD="class: xl67, align: center"]7/20/2018[/TD]
[TD="class: xl67, align: center"]8/8/2018[/TD]

</tbody>

Cheers,

STF

 

[Peter_SSs]

I can't see how this relates to your original formula & description at all.

 

[SHUTTEHFACE]

I can't see how this relates to your original formula & description at all.

=SUMPRODUCT(--(B2:B15="THC"),--(AK2:AK15<=TODAY()-90),--(AM2:AM15<=TODAY()-90),--ISNUMBER(SEARCH("|"&E2:E15&"|","|1, THC Pilot|1, Both Pilot|2, THC Pilot|2, Both Pilot|3, THC Pilot|3, Both Pilot|")))where group = b2:b5
Received = ak2:ak15Final approval = am2:am15
Priority = e2:15

Cheers,

STF

 

[SHUTTEHFACE]

Any ideas? Hoping to get this working soon

Also tried to simplify but still not working

=SUMPRODUCT(--(B2:B15="THC"),--(AK2:AK15<=TODAY()-90),--(AM2:AM15<=TODAY()-90),[COLOR=#0000CD]--(E2:E15="*Pilot*")))[/COLOR]

 

[SHUTTEHFACE]

Also tried

=SUMPRODUCT(--(ISNUMBER(SEARCH("THC",$B:$B))),--(ISNUMBER(SEARCH("*Pilot*",$E:$E))),--($AK:$AK>0),--(DATEDIF($AK:$AK,TODAY(),"d")+1<90),--($AMO:$AM>0),--(DATEDIF($AM:$AM,TODAY(),"d")+1<90))

But am getting # value error