countifs filtering

asked 2020-03-26 21:31:38 +0200

daverc gravatar image

updated 2020-03-27 17:14:54 +0200

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")C:\fakepath\example.ods

edit retag flag offensive close merge delete

Comments

Please attach a sample file with the issue.

m.a.riosv gravatar imagem.a.riosv ( 2020-03-27 00:44:00 +0200 )edit

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

daverc gravatar imagedaverc ( 2020-03-27 17:19:08 +0200 )edit