Excel 2003 - COUNTIFS

ryangavin777

New Member
Joined
Dec 7, 2016
Messages
2
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-14Date of IncidentTypeDriver Payroll No
Date To:01-Jan-16 25-Nov-15Apple555551
18-Sep-15Orange555551
15-May-14Apple555551
22-Jan-14Orange555554
04-May-14Orange555555
12-Jan-15Orange555556
09-May-15Apple555557
08-Jan-15Apple555558
12-Jul-14Apple555559
21-Dec-14Orange555560
24-Mar-14Orange555561
27-Nov-15Orange555562
06-Jan-15Apple555563
09-Sep-15Apple555564
18-Jan-14Apple555565
27-Oct-15Orange555566
09-May-15Orange555567
10-Apr-14Orange555568
30-Jun-15Apple555569
18-Oct-14Apple555570
31-Aug-14Apple555571
29-Oct-14Orange555572
19-Oct-14Orange555573
30-Jan-14Orange555574


Thank you!
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

ryangavin777

New Member
Joined
Dec 7, 2016
Messages
2
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-14Date of IncidentTypeDriver Payroll No X
Date To:01-Jan-1601-Jan-14Apple5555513
06-Jan-15Apple5555513
06-Feb-15Apple5555513
06-Mar-15Orange5555540
06-Apr-15Orange5555550
06-May-15Orange5555560
06-Jun-15Apple5555571
06-Jul-15Apple5555581
06-Aug-15Apple5555591
06-Sep-15Orange5555600
06-Oct-15Orange5555610
06-Nov-15Orange5555620
06-Dec-15Apple5555631
06-Jan-16Apple5555640
06-Feb-16Apple5555650
06-Mar-16Orange5555660
06-Apr-16Orange5555670
06-May-16Orange5555680
06-Jun-16Apple5555690
06-Jul-16Apple5555700
06-Aug-16Apple5555710
06-Sep-16Orange5555720
06-Oct-16Orange5555730
06-Nov-16Orange5555740

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


I hope that makes sense!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,101
Members
416,161
Latest member
David1966Lewis

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top