COUNTIFS AND OR in MSexcel: therefore SUMPRODUCT AND OR(?)

[Dee Dee]

I am having an issue with the "old" version excel still. Each new addition to a formula seems to make things more difficult. In this case, I need to make L, M and N involve multiple criteria with AND and OR.

Formal complaint tracker a:a =<180 AND J:J="N" AND T:T="N"
OR if A:A>360 AND J:J="Y" OR T:T="Y"

This would be for L and I can figure our the other two. Again, I am having issues with my xl2bb paste. I really do not know what I am doing wrong because it had been working before.

Note:
The first few formulas I did on this file it was about 430kb, after it was returned to me for additional work it is now 45,220kb. I assume that this may be a part of the problem.

Tracker new version.xlsx
	A		C	D	E	F	G	H	I	J	K	L	M	N	O
1	As of :		Carryover from prior FY	Filed in Current FY	Total (B+C)	Pending Acknowledgment	Pending Acceptance or Dismissal	Accepted	Dismissed	Amended	Pending Investigation	Investigation Completed Timely	Investigation Untimely	Average Days in Investigation	Settlement
2
3
4	FY2022		0	0	0	0	0	0	0	0	0	0			0
5	FY2021		3	3	6	2	0	2	1	1	2	0			1
6	FY2020		6	6	12	6	3	2	1	0	2	7			1
7	FY2019		0	0	0	0	0	0	0	0	0	0			0
8
9	1
10
11
12
13
Formal Director's Update

Cell Formulas
Range	Formula
C4	C4	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$3),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$Z$2:$Z$3000>$A$9))
D4:D7	D4	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=B4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<B3))
E4:E7	E4	=C4+D4
F4	F4	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$3),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$G$2:$G$3000=""))
G4	G4	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$3),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000=""))
H4	H4	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$3),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="acceptance")--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Partial Dismissal"))
I4	I4	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$3),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000=" Dismissal"))
J4	J4	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$3),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$J$2:$J$3000="Y"))
K4	K4	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$3),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Acceptance")--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Partial Dismissal"))
L4	L4	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$3),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$A$2:$A$3000>=180)--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$J$2:$J$3000="N")--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$T$2:$T$3000="N"))
C5	C5	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$Z$2:$Z$3000>$A$9))
F5	F5	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$G$2:$G$3000=""))
G5	G5	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000=""))
H5	H5	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="acceptance")--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Partial Dismissal"))
I5	I5	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Dismissal"))
J5	J5	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$J$2:$J$3000="Y"))
K5	K5	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Acceptance")--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Partial Dismissal"))
L5	L5	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$A$2:$A$3000>=180)--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$J$2:$J$3000="N")--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$T$2:$T$3000="N"))
C6	C6	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$Z$2:$Z$3000>$A$9))
F6	F6	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$G$2:$G$3000=""))
G6	G6	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000=""))
H6	H6	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="acceptance")--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Partial Dismissal"))
I6	I6	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Dismissal"))
J6	J6	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$J$2:$J$3000="Y"))
K6	K6	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Acceptance")--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Partial Dismissal"))
L6	L6	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$A$2:$A$3000>=180)--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$J$2:$J$3000="N")--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$T$2:$T$3000="N"))
C7	C7	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$7),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$Z$2:$Z$3000>$A$9))
F7	F7	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$7),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$G$2:$G$3000=""))
G7	G7	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$7),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000=""))
H7	H7	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$7),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="acceptance")--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Partial Dismissal"))
I7	I7	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$7),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Dismissal"))
J7	J7	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$7),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$J$2:$J$3000="Y"))
K7	K7	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$7),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Acceptance")--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$H$2:$H$3000="Partial Dismissal"))
L7	L7	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$7),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$A$2:$A$3000>=180)--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$J$2:$J$3000="N")--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$T$2:$T$3000="N"))
N5	N5	=IFERROR(AVERAGEIFS('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!Y:Y,'C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!F:F,">=10/1/2020",'C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!F:F,"<=9/30/2021"),"")
O4	O4	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$3),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$AG$2:$AG$3000="Y"))
O5	O5	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$4),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$AG$2:$AG$3000="Y"))
O6	O6	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$5),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$AG$2:$AG$3000="Y"))
O7	O7	=SUMPRODUCT(--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000>=$B$7),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$F$2:$F$3000<$B$6),--('C:\Users\deish\Downloads\[Complaints Tracker - FY21.xlsx]Formal Complaint Tracking'!$AG$2:$AG$3000="Y"))

 

[sijpie]

If the formulas used to work OK and now have stopped working, this is surely because of the additional hidden corrupted stuff that has bloated the workbook to 45 MB.

To get that corrected is a bit of work.
Make a new workbook
Set up a sheet with the same formatting as this current sheet. Don't copy this sheet across!!

Now take one cell and rebuild the formula in here (you can copy the formula from the old workbook to the new. Don't copy the cell)
Check that it works. Then rebuild the other cells. By modifying your absolute references to semi absolute you will be able to copy down or across a lot of the formulas.