Excel 2003 - COUNTIFS

[ryangavin777]

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!

 

[Joe4]

Before COUNTIFS, we used SUMPRODUCT (which still works).
See: https://exceljet.net/formula/count-if-two-criteria-match
and http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[ryangavin777]

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