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