# countifs filtering

I’ve found an issue with an equation i wrote. I have two columns that can hold one of two inputs. Column "M’ can have ether “E” or “MS” or nothing and column “N” can have "AP or “UP” or nothing and “Q” can have any number from 0 to something greater then zero.

My problem is I’m getting a double count when I should get only one if the column “Q” is “>0”. There are scenario’s when for a given row both column’s “M” which can have “E” or “MS” and “N” will have “AP” at the same time. When that happens which is rare, I get a double count.

=–(COUNTIFS(Q37:Q61,">0",M37:M61,"<>E",M37:M61,"<>MS",N37:N61,"<>AP",N37:N61,"<>UP"))+(COUNTIFS(Q70:Q94,">0",M70:M94,"<>E",M70:M94,"<>MS",N70:N94,"<>AP",N70:N94,"<>UP"))+(COUNTIFS(Q101:Q125,">0",M101:M125,"<>E",M101:M125,"<>MS",N101:N125,"<>AP",N101:N125,"<>UP"))+(COUNTIFS(Q135:Q159,">0",M135:M159,"<>E",M135:M159,"<>MS",N135:N159,"<>AP",N135:N159,"<>UP"))+(COUNTIFS(Q166:Q190,">0",M166:M190,"<>E",M166:M190,"<>MS",N166:N190,"<>AP",N166:N190,"<>UP"))+(COUNTIFS(Q200:Q224,">0",M200:M224,"<>E",M200:M224,"<>MS",N200:N224,"<>AP",N200:N224,"<>UP")) &" of "& G7-G31-COUNTA(L6:L30)-COUNTIFS(M37:M224,“E”)-COUNTIFS(M37:M224,“MS”)-COUNTIFS(N37:N224,“UP”)example.ods

Please attach a sample file with the issue.

I added a copy that shows the problem. In Cell 13 the left hand number should never be larger then the right hand side. I also placed at Cells M and N81, M and N38 the combination that causes the problem