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

 

[Peter_SSs]

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

That is still the case.
I can't tell what columns I am looking at in post 6 but I presume "Received" and "Final approval" are columns AK and AM in your formula. If so, there appears to be only 2 rows in that data where both dates are more than 90 days old so I have no idea how you (manually) get a result of 7.

Further, you have shown a cell containing "THC pilot". Does that have anything to do with the count you are trying to calculate? If so, what cell is it and how does it relate to the problem.

BTW, posting formulas that you have tried and do not work, doesn't actually help. What helps the most is accurate sample data and expected results and a clear explanation of how the result(s) would be obtained manually.
It is also much easier if we can see what rows/columns any sample data is in and still copy that data to our sheets to test. Links in my signature block below can help with that.