count of cells based between 2 dates, removing duplicates from criteria based on 3 other columns

fishep6

New Member
Joined
Feb 10, 2014
Messages
43
I am looking for a couple of tweaks to an existing formula which I just cant find the logic to

What I am trying to do is create a formula that will count all the dates in column “U” based on a criteria of a date that is in cell “I4” (this is a fixed date).

What I want to do is to make this count in column “U” ignore duplicate values in column “D”. So based on the table below the formula I am using return the count of 4 as there are 2duplicate entries in column A "A - 01/01/1996" and "D - 19/09/1994" so I want each counted once not twice if that makes sense?


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"] COLUMN[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]AI[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]ROW[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]01/01/1996[/TD]
[TD="align: center"]01/01/2015[/TD]
[TD][TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]01/01/1997[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]01/01/1996[/TD]
[TD="align: center"]01/01/2015[/TD]
[TD="align: center"]01/01/1990[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]02/04/1995[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]06/02/2012[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]19/09/1994[/TD]
[TD="align: center"]01/01/2015[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]19/09/1994[/TD]
[TD="align: center"]01/01/2015[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]22/05/2017[/TD]
[TD="align: center"]01/01/2015[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]22/05/2017[/TD]
[TD="align: center"]01/01/2015[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]13/06/1989[/TD]
[TD="align: center"]01/01/2015[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


















The formula I have is below and currently works

=SUMPRODUCT((ATKE!U4:U991<>"")*(ATKE!U4:U991<=$I$4)*(MATCH(ATKE!$D$4:$D$991&"|"&ATKE!U4:U991,ATKE!$D$4:$D$991&"|"&ATKE! U4:U991,0)=ROW(ATKE!$D$4:$D$991)-3))
However I want to make 2 tweaks to this formula:


  1. How would I tweak this to make it look between 2 dates rather than just either side of 1 date (“I4” at present)

    Currently “I4” is the upper date I am using in the formula but I want to be able to set cell ”I5” as the lower date therefore the range in the formula will look between (date ranges in-between (and including the date itself) “I5” and “I4”.

2. The next element is I need to add another dimension into the formula as well as the data range as mentioned above.

At the moment the formula looks at and counts the number of unique dates in column U based on removing duplicate entries in column D and gives me a count of 4 (as per table above).

What I then really need to do is split this example of 4 numbers into 2 further categories based on data in column “AI” (still with the same range of the present formula of AI4:AI991)

the split I want is how many unique values of those identified with the original formula (plus the new date range set in task 1) also have a date in column “AI” versus a blank in column “AI”, so in this scenario the 4 we have identified, 3 of them have a date in column “AI” which is relevant and so the count should produce an answer of 3.

Many thanks in advance for your help
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sorry, just to be clear the date ranges stated in column "I" only affect column "U" and don't apply to the dates in column "AI".
 
Upvote 0

Forum statistics

Threads
1,223,954
Messages
6,175,603
Members
452,658
Latest member
GStorm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top