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?
<tbody>
</tbody>
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:
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
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?
| COLUMN | D | U | AI | I | ||
| ROW | ||||||
| 4 | A | 01/01/1996 | 01/01/2015 |
<tbody> </tbody> | ||
| 5 | A | 01/01/1996 | 01/01/2015 | 01/01/1990 | ||
| 6 | B | 02/04/1995 | ||||
| 7 | C | 06/02/2012 | ||||
| 8 | D | 19/09/1994 | 01/01/2015 | |||
| 9 | D | 19/09/1994 | 01/01/2015 | |||
| 10 | E | 22/05/2017 | 01/01/2015 | |||
| 11 | E | 22/05/2017 | 01/01/2015 | |||
| 12 | F | 13/06/1989 | 01/01/2015 |
<tbody>
</tbody>
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:
- 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
