# Excel 2003 - COUNTIFS

#### ryangavin777

##### New Member
Hello,

The business I work for unfortunately only have excel 2003. Therefore, any spreadsheets I do must be 2003 proof. I need some help on how I would do this with the ability to have COUNTIFS please!

What I need in the 'COUTIFS (RESULT)' column is:

- Count the number 'Drivers' who have had >=2 'Apple' between 01-Jan-14 and 01-Jan-16.

 Date From: 01-Jan-14 Date of Incident Type Driver Payroll No Date To: 01-Jan-16 25-Nov-15 Apple 555551 18-Sep-15 Orange 555551 15-May-14 Apple 555551 22-Jan-14 Orange 555554 04-May-14 Orange 555555 12-Jan-15 Orange 555556 09-May-15 Apple 555557 08-Jan-15 Apple 555558 12-Jul-14 Apple 555559 21-Dec-14 Orange 555560 24-Mar-14 Orange 555561 27-Nov-15 Orange 555562 06-Jan-15 Apple 555563 09-Sep-15 Apple 555564 18-Jan-14 Apple 555565 27-Oct-15 Orange 555566 09-May-15 Orange 555567 10-Apr-14 Orange 555568 30-Jun-15 Apple 555569 18-Oct-14 Apple 555570 31-Aug-14 Apple 555571 29-Oct-14 Orange 555572 19-Oct-14 Orange 555573 30-Jan-14 Orange 555574

Thank you!

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Thanks for your help. I've got 80% there now The formula I have in column X is....

=SUMPRODUCT(--(\$I\$6:\$I\$29="Apple")*(\$J\$6:\$J\$29=\$J7)*(\$H\$6:\$H\$29>=\$F\$5)*(\$H\$6:\$H\$29<=\$F\$6))

This has obviously worked but I need something else that I can't figure out!

What I need is basically a 24 month rolling tally of drivers who had >=2 "apple" between the two dates below. i.e. Driver 555551 needs to be counted as 1 instances not 3. Similarly if the driver had 4 or 5 apples it would still need to be shown as 1 instances. I can then count the number of drivers to get a 24 month rolling tally.

 Date From: 01-Jan-14 Date of Incident Type Driver Payroll No X Date To: 01-Jan-16 01-Jan-14 Apple 555551 3 06-Jan-15 Apple 555551 3 06-Feb-15 Apple 555551 3 06-Mar-15 Orange 555554 0 06-Apr-15 Orange 555555 0 06-May-15 Orange 555556 0 06-Jun-15 Apple 555557 1 06-Jul-15 Apple 555558 1 06-Aug-15 Apple 555559 1 06-Sep-15 Orange 555560 0 06-Oct-15 Orange 555561 0 06-Nov-15 Orange 555562 0 06-Dec-15 Apple 555563 1 06-Jan-16 Apple 555564 0 06-Feb-16 Apple 555565 0 06-Mar-16 Orange 555566 0 06-Apr-16 Orange 555567 0 06-May-16 Orange 555568 0 06-Jun-16 Apple 555569 0 06-Jul-16 Apple 555570 0 06-Aug-16 Apple 555571 0 06-Sep-16 Orange 555572 0 06-Oct-16 Orange 555573 0 06-Nov-16 Orange 555574 0

<colgroup><col span="5"><col><col></colgroup><tbody>
</tbody>

I hope that makes sense!!

Replies
5
Views
367
Replies
2
Views
310
Replies
33
Views
2K
Replies
6
Views
1K
Replies
3
Views
898

1,206,811
Messages
6,074,998
Members
446,112
Latest member
nmz1133

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