COUNTIFS Comparing Dates

[surkdidat]

I have the below formula, but also need it to take into account occurrences when Column K (in DEV WIP) is less than Column L in DEV WIP

=
COUNTIFS(
'DEV WIP'!K:K,"<>31/12/9999",
'DEV WIP'!N:N,"<>COMPLETE",
'DEV WIP'!K:K,"<"&TODAY()+30,
'DEV WIP'!K:K,">="&TODAY(),
'DEV WIP'!L:L,"<>")

 

[tyija1995]

Hey try this:

=SUMPRODUCT(('DEV WIP'!K:K<>DATE(9999,12,31))*('DEV WIP'!N:N<>"COMPLETE")*('DEV WIP'!K:K < <today()+30)*('dev wip'!k:k="">TODAY() + 30)*('DEV WIP'!K:K >= TODAY())*('DEV WIP'!L:L<>"")*('DEV WIP'!K:K<'DEV WIP'!L:L))</today()+30)*('dev>

 

[surkdidat]

Thankyou

Hey try this:

=SUMPRODUCT(('DEV WIP'!K:K<>DATE(9999,12,31))*('DEV WIP'!N:N<>"COMPLETE")*('DEV WIP'!K:K < <today()+30)*('dev wip'!k:k="">TODAY() + 30)*('DEV WIP'!K:K >= TODAY())*('DEV WIP'!L:L<>"")*('DEV WIP'!K:K<'DEV WIP'!L:L))</today()+30)*('dev>

 

[pgc01]

Hi

...
'DEV WIP'!K:K,"<>31/12/9999",
...
'DEV WIP'!K:K,"<"&TODAY()+30,
...


Why the first condition?
If it's less than (TODAY()+30) then it will never be equal to 31/12/9999.

Please explain.

 

[tyija1995]

I noticed that too pgc, thought it looked a bit strange kept the formula as is though but indeed it can most likely be removed from the formula.