Excel 2016 to 97-2003 Compatibility Issue

[DJFANDANGO]

Hi Guy's,

I have a formula that works great in Excel 2016, but it doesn't work on Excel 97-2003... I guess it's a COUNTIFS issue, as the earlier versions did not have this...?

How do I re-write this formula?

=COUNTIFS('SOP Data'!$C$3:$C$5000,$A5,'SOP Data'!$B$3:$B$5000,">="&AR$1,'SOP Data'!$B$3:$B$5000,"<="&AR$2)

Many thanks!

 

[RoryA]

You can use SUMPRODUCT instead:

=SUMPRODUCT(('SOP Data'!$C$3:$C$5000=$A5)*('SOP Data'!$B$3:$B$5000>=AR$1)*('SOP Data'!$B$3:$B$5000<=AR$2))
​

 

[DJFANDANGO]

Thanks Rory,

I've tried this and it's not working for me...

 

[Dave Patton]

N.B.
Try this example and then adapt to your actual data.

T202010a.xlsm
	A	B	C	D
1	Criteria	Data
2	1-May-20			2
3	30-May-20	1-Jan-20	ABC
4		1-Apr-20
5	ABC	1-May-20	ABC
6		1-May-20	DD
7		20-May-20	ABC
8		1-Jun-20	ABC
6a

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--($B$3:$B$5000>=A2),--($B$3:$B$5000<=A3),--($C$3:$C$5000=A5))