Counting distinct occurrences between two columns based on date

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.

Please Login or Register  to view this content.

Can anyone assist?

Cheers,

STF

How does that formula not do what you want?

You clearly have some other criteria besides just the age of the dates. Can you explain further? Best of all would be to attach your file so we can test alternatives with your data.

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.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Thanks, I'm trying to add the attachment but having some difficulties. I can add upload it but can't seem to add it to my post. the "attachments" button is blank.

I am not able to see connections between expected returns, the instructions and the upload.

However I did manage to get 5 for less than 90 days with this.

Formula:

Please Login or Register  to view this content.

I took a few more guesses. Returns 7 for greater than 90 days

Formula:

Please Login or Register  to view this content.

Returns 5 for less than 90 days.

Formula:

Please Login or Register  to view this content.

Thanks, but I need to include both columns C & D. For example, the total count of dates greater than 90 days between both columns would be 7. That's why I originally thought the sumproduct approach would work. Adding a little more context for the sample I provided... If the date in column D is <90 days it should trump the value in column C which should then not be counted in the final total. Hope that helps

This part is confusing.

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

Those are only in one column. The "THC" part can cover the various "THC Pilots" with wildcards.

SUMPRODUCT requires extra function calls to find those array constant strings. It also has to reconcile blanks in the dates and assure that TODAY()-0 does not occur.

COUNTIFS covers all of those very easily.

All that is now required is a clearer explanation of what you need done to get the expected 7 and 5.

total count of dates greater than 90 days between both columns would be 7.

I thought there were additional qualifiers. The rest of what you've described doesn't reconcile this.

Thanks, I realize I'm not doing a great job of explaining it

If you look at the example I shared, I want to count all dates in Sheet 1 between columns C and D that are less than 90 days old. The caveat is that I'm trying to count the most recent date shared between "Received" and "Final Approval". So for example the first row, D2 would count as +1 for <90 days since 8/8/2018 is less than 90 days old, and we would not count the value in C2. Same thing for row 3. No count for row 4 since there is no data. Row 7 would count as +1 for data greater than 90 days old since there is nothing in column D to trump that information.

So, IF both column D and C have data count only the date in column D. IF only column C has data count only the date in column C. IF neither column has data do not counts anything.

Hopefully that helps

I will have look at it later tonight or tomorrow.

I'm working on some other projects at the moment.

What happened to this part from Post #3.

... that are older than 90 days ...

Now in Post #10 you are saying

... that are less than 90 days ...

Also:
Please post formulas that relate to the upload you posted. The ranges have not been the same as the workbook. Referencing different ranges confuses things. It may be more the source of the problems you are encountering.

Thanks, I've attached a new version of the excel example with the formula in the cells in sheet 2 and an example of what I am trying to count in sheet 1. Hope this helps.

How can data from 20/03/2018 be less than 90 days old when there have been 148 days since then?

Anyhow, given the errors in your manual calculations, try this:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G
1	group	Priority	Received	Final approval		Data more than 90 days old	Data less than 90 days old
2	THC	3, THC Pilot	17/04/2018	08/08/2018			1
3	THC	1, THC Pilot	07/08/2017	08/08/2018			1
4	THC	2, Both Pilot
5	THC	1, Both Pilot	25/09/2017	08/08/2018			1
6	THC	1, Both Pilot	15/08/2017	08/08/2018			1
7	THC	3, Both Pilot	18/09/2017			1
8	THC	1, Both Pilot	15/08/2017			1
9	THC	1, THC Pilot	06/07/2017			1
10	THC	1, THC Pilot	28/08/2017	20/03/2018		1
11	THC	1, Both Pilot	15/08/2017			1
12	THC	1, Both Pilot	11/07/2017	20/01/2018		1
13	THC	1, Both Pilot	25/09/2017			1
14	THC	2, Both Pilot	20/07/2018	08/08/2018			1
15
16						7	5

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	F	G
2	=IF(COUNT(C2:D2)=0,"",IF(DATEDIF(MAX(C2:D2),TODAY(),"d")>90,1,""))	=IF(COUNT(C2:D2)=0,"",IF(DATEDIF(MAX(C2:D2),TODAY(),"d")<=90,1,""))

Sheet: Sheet1

Forget the above - I have just realised it's not what you want.

On sheet1 it shows 'Data more than 90 days old' in F16 = 5.
On same sheet it shows 'Data less than 90 days old' G16 = 7.

On expected results sheet2 B4:C4 it is/has been showing exactly the opposite.

Originally Posted by SHUTTEHFACE

Thanks for the help! It sounds like you at least know what I'm trying to calculate!

I've just read through the thread again.

Could you please look at the suggestion I gave you and tell me what I got wrong? Where is it failing? I think it is nearly there but perhaps not quite.

What it does is take the later of the two dates and then decides which column to count it in.

I think I have it.

For greater than 90:

=SUMPRODUCT(($A$2:$A$14="THC")*($C$2:$C$14<>"")*($D$2:$D$14="")*IFERROR((TODAY()-$C$2:$C$14)>90,""))+SUMPRODUCT(($A$2:$A$14="THC")*($C$2:$C$14<>"")*($D$2:$D$14<>"")*IFERROR((TODAY()-$D$2:$D$14)>90,""))

and for less than or equal to 90:

=SUMPRODUCT(($A$2:$A$14="THC")*($C$2:$C$14<>"")*($D$2:$D$14="")*IFERROR((TODAY()-$C$2:$C$14)<=90,""))+SUMPRODUCT(($A$2:$A$14="THC")*($C$2:$C$14<>"")*($D$2:$D$14<>"")*IFERROR((TODAY()-$D$2:$D$14)<=90,""))

Make sure your cells are set to general or number format.

So, was my latest suggestion of any use? Or is it still not what you are after?

Thanks for your help! AliGW. It is mostly working

I noticed a couple things:
1. when I add dates to cell D4 the count doesn't update.
2. when I add a date to D7, that is still more than 90 days old, it counts both the cells in C7 and D7

Attach a sample workbook. It’s working for me, so I need to see what you’ve done.

EDIT: Based on what you said before, I inferred there should not be a date in D4 whilst C4 remains empty - are you now saying that C4 could be empty and a date in D4???

The caveat is that I'm trying to count the most recent date shared between "Received" and "Final Approval".

Can the received data ever be earlier than the final approval date?

Try these adjustments:

=SUMPRODUCT(($A$2:$A$14="THC")*($C$2:$C$14<>"")*($D$2:$D$14="")*IFERROR((TODAY()-$C$2:$C$14)>90,""))
+SUMPRODUCT(($A$2:$A$14="THC")*($D$2:$D$14<>"")*IFERROR((TODAY()-$D$2:$D$14)>90,""))

=SUMPRODUCT(($A$2:$A$14="THC")*($C$2:$C$14<>"")*($D$2:$D$14="")*IFERROR((TODAY()-$C$2:$C$14)<=90,""))
+SUMPRODUCT(($A$2:$A$14="THC")*($D$2:$D$14<>"")*IFERROR((TODAY()-$D$2:$D$14)<=90,""))

Thanks! See attached, I am still not getting it to sum correctly and also need to make sure the formula checks that the value in column B has the word "Pilot".

I didn’t know about the pilot bit - sorry.

Won’t be able to look at it again until the morning. Have you added your expected results manually to the workbook? Have you explained in what way it isn’t working?

So this is my final attempt:

=SUMPRODUCT((ISNUMBER(SEARCH("pilot",$B$2:$B$14)))*($C$2:$C$14<>"")*($D$2:$D$14="")*IFERROR((TODAY()-$C$2:$C$14)>90,0))
+SUMPRODUCT((ISNUMBER(SEARCH("pilot",$B$2:$B$14)))*($D$2:$D$14<>"")*IFERROR((TODAY()-$D$2:$D$14)>90,0))

and:

=SUMPRODUCT((ISNUMBER(SEARCH("pilot",$B$2:$B$14)))*($C$2:$C$14<>"")*($D$2:$D$14="")*IFERROR((TODAY()-$C$2:$C$14)<=90,0))
+SUMPRODUCT((ISNUMBER(SEARCH("pilot",$B$2:$B$14)))*($D$2:$D$14<>"")*IFERROR((TODAY()-$D$2:$D$14)<=90,0))

Both need to be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

You can change "pilot" to "THC pilot" if you want to limit it further, or even use a cell reference for the words that need to appear in column B.

NB: Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Thank you! I was able to get this working, appreciate the support, have a great weekend.

You're welcome!

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.