Building a Formula

[cjgrafixdesign]

HELP :D

I'm building a formula that would find between two dates and SUM which if found this on your site very useful formula

=COUNTIFS(AH9:AJ60,">="&AA64,AH9:AJ60,"<="&AF64)

The formula helps me find all the dates I needed to count within certain worksheets.

for example I have this

START DATE (OCTOBER 1, 2018)
END DATE (OCTOBER 31, 2018)

Within 12 worksheet it did find all dates between October 1 to October 31.



however, i'd like to ask if .. it is possible for a formula that aside from finding those dates between. is it possible to have it SUM/DIFF into another date and it will only COUNTIF if it is greater than 3.


example data:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A<strike></strike>
[/TD]
[TD="align: center"]B<strike></strike>
[/TD]
[TD="align: center"]C <strike></strike>
[/TD]
[/TR]
[TR]
[TD]DESCRIPTION<strike></strike>
[/TD]
[TD]DATE FILE <strike></strike>
[/TD]
[TD] DATE PROCESS <strike></strike>
[/TD]
[/TR]
[TR]
[TD]APPLE <strike></strike>
[/TD]
[TD]10/05/2018 <strike></strike>
[/TD]
[TD]10/10/2018 <strike></strike>
[/TD]
[/TR]
[TR]
[TD]GRAPES<strike></strike>
[/TD]
[TD]11/05/2018 <strike></strike>
[/TD]
[TD]11/10/2018 <strike></strike>
[/TD]
[/TR]
[TR]
[TD]ORANGE<strike></strike>
[/TD]
[TD]12/05/2018
[/TD]
[TD] 12/05/2018 <strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]


=COUNTIFS(AH9:AJ60,">="&AA64,AH9:AJ60,"<="&AF64) NOTE: not the actual cell num

using the above formula will find the October, using column DATE PROCESS....after finding the date is it possible to also test C-B (as long as it is under October) and will only display a value greater than 3 days

sorry for my first post newbies here :D

 

[steve the fish]

If ive understood try this with the 1st october in E1:

=SUMPRODUCT(--($C$2:$C$4>=$E$1),--($C$2:$C$4 < EDATE(E1,1)),--($C$2:$C$4-$B$2:$B$4>3))<edate(e1,1)),--($c$2:$c$4-$b$2:$b$4></edate(e1,1)),--($c$2:$c$4-$b$2:$b$4>

 

[cjgrafixdesign]

[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Document Registered[/TD]
[TD]Date Filed
[/TD]
[TD]Date Process
[/TD]
[TD]No. of Days Process
Greater Than 3
[/TD]
[/TR]
[TR]
[TD]DRRR
[/TD]
[TD]02/01/2018
[/TD]
[TD]02/07/2018
[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]ATTENDANCE
[/TD]
[TD]02/02/2018
[/TD]
[TD]02/03/2018
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GATEPASS
[/TD]
[TD]02/03/2018
[/TD]
[TD]02/10/2018
[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD]LEAVE FORM
[/TD]
[TD]05/01/2018
[/TD]
[TD]05/05/2018
[/TD]
[TD]

​

[/TD]
[/TR]
[TR]
[TD]TRAVEL ORDER
[/TD]
[TD]05/22/2018
[/TD]
[TD]05/23/2018
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]START
[/TD]
[TD]END
[/TD]
[TD]TOTAL
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]February 1, 2018
[/TD]
[TD]February 28, 2018
[/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=COUNTIFS(C2:C6,">="&A9,C2:C6,"<="&B9)

Using the above formula will help me locate all FEBRUARY 2018 dates.

now, in the column of D2-D6 are results of difference between C-B only if they are under FEBRUARY and it is greater than 3