COUNTIFS - Multiple Conditions with Cell Reference

I'm trying to count up the data points that meet a series of conditions (about 7 or 8 conditions in total). All of these data points are in my "All Data (Cleaned Up)" worksheet and I am using a COUNTIFS formula. As of now, this formula IS producing the expected result:

=COUNTIFS('All Data (Cleaned Up)'!$E$2:$E$1000000,">="&A12,'All Data (Cleaned Up)'!$E$2:$E$1000000,"<="&B12,'All Data (Cleaned Up)'!$L$2:$L$1000000,"=FALSE",'All Data (Cleaned Up)'!$S$2:$S$1000000,">""",'All Data (Cleaned Up)'!$AA$2:$AA$1000000,">""178",'All Data (Cleaned Up)'!$AB$2:$AB$1000000,"Dollars",'All Data (Cleaned Up)'!$B$2:$B$1000000,">="&$B$1,'All Data (Cleaned Up)'!$B$2:$B$1000000,"<="&$B$2,'All Data (Cleaned Up)'!$F$2:$F$1000000,"=YES")

I'd like to make a change to one of the conditions -- specifically the condition that's based on column AA. Currently, the formula looks for values in that column that are greater than 178. Notice that there is no & between the ">" and the "178", but the formula works. When I put an & in between those two strings, the formula actually stops working (it computes to 0 - as if there are no matches).

Anyways, I'd like to make two changes to the formula. First, I'd like to change the operator from ">" to ">=". I'd also like to replace the reference to "178" with a cell reference (so that the number I'm using for the >= comparison can be dynamic). The cell reference I'm trying to use for the dynamic input is cell B4 in the current worksheet.

I have tried the following formula, but it does not work (it computes to 0 - as if there are no matches).

=COUNTIFS('All Data (Cleaned Up)'!$E$2:$E$1000000,">="&A12,'All Data (Cleaned Up)'!$E$2:$E$1000000,"<="&B12,'All Data (Cleaned Up)'!$L$2:$L$1000000,"=FALSE",'All Data (Cleaned Up)'!$S$2:$S$1000000,">""",'All Data (Cleaned Up)'!$AA$2:$AA$1000000,">="&B4,'All Data (Cleaned Up)'!$AB$2:$AB$1000000,"Dollars",'All Data (Cleaned Up)'!$B$2:$B$1000000,">="&$B$1,'All Data (Cleaned Up)'!$B$2:$B$1000000,"<="&$B$2,'All Data (Cleaned Up)'!$F$2:$F$1000000,"=YES")

Any suggestions of how I can accomplish what I'm trying would be welcome! Thanks!

Originally Posted by Epilogue

Notice that there is no & between the ">" and the "178", but the formula works.

The formula is merely syntactically valid. The ">""178" is interpreted as greater than the TEXT "178, that is, a double quote character followed by 178. The two sequential double quotes are treated as a single literal double quote character in the text constant.

Does 'All Data (Cleaned Up)'!$AA$2:$AA$1000000 contain text comprised of decimal numerals? Note that if B1 contained ="177", producing TEXT "177", =COUNT(B1,">""178") returns 1. Why? Because Excel's collation sequence considers the double quote character less than the numeral 1. Change the formula to =COUNTIF(B1,">"&"178"), and the changed formula returns 0.

What does the formula =SUMPRODUCT(ISNUMBER('All Data (Cleaned Up)'!$AA$2:$AA$1000000)+('All Data (Cleaned Up)'!$AA$2:$AA$1000000>"178")) return? If it returns 0, then there are no numeric values in that range, AND there are no text values greater than "178".

Your 2nd formula should work, but you need to check your data. See the formula in the immediately preceding paragraph.

Originally Posted by hrlngrv

The formula is merely syntactically valid. The ">""178" is interpreted as greater than the TEXT "178, that is, a double quote character followed by 178. The two sequential double quotes are treated as a single literal double quote character in the text constant.

Does 'All Data (Cleaned Up)'!$AA$2:$AA$1000000 contain text comprised of decimal numerals? Note that if B1 contained ="177", producing TEXT "177", =COUNT(B1,">""178") returns 1. Why? Because Excel's collation sequence considers the double quote character less than the numeral 1. Change the formula to =COUNTIF(B1,">"&"178"), and the changed formula returns 0.

What does the formula =SUMPRODUCT(ISNUMBER('All Data (Cleaned Up)'!$AA$2:$AA$1000000)+('All Data (Cleaned Up)'!$AA$2:$AA$1000000>"178")) return? If it returns 0, then there are no numeric values in that range, AND there are no text values greater than "178".

Your 2nd formula should work, but you need to check your data. See the formula in the immediately preceding paragraph.

The second I read your response, I knew you had identified the issue. My numbers were parsed out of a string. I converted them to numbers, and it all works perfectly now.


Thanks!