# Building a Formula

#### cjgrafixdesign

##### New Member
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:

 A B C DESCRIPTION DATE FILE DATE PROCESS APPLE 10/05/2018 10/10/2018 GRAPES 11/05/2018 11/10/2018 ORANGE 12/05/2018 12/05/2018

<tbody>
</tbody>

=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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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>

Last edited:
 A B C D Document Registered Date Filed Date Process No. of Days Process Greater Than 3 DRRR 02/01/2018 02/07/2018 5​ ATTENDANCE 02/02/2018 02/03/2018 GATEPASS 02/03/2018 02/10/2018 7​ LEAVE FORM 05/01/2018 05/05/2018 ​ TRAVEL ORDER 05/22/2018 05/23/2018 START END TOTAL February 1, 2018 February 28, 2018 3

<tbody>
</tbody>

=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

Replies
6
Views
95
Replies
5
Views
206
Replies
1
Views
153
Replies
5
Views
457
Replies
7
Views
416

1,196,030
Messages
6,012,962
Members
441,741
Latest member
jlburn

### 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.

### Which adblocker are you using?

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

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