Excel Countifs formula

Question

I have nested the below IF/AND/COUNTIFS formula. The logic is based on the country in column N, refer to specific range on another sheet in the same WB. The problem I am running into is I am getting FALSE results when it should be TRUE. I have triple checked all my ranges. And all values on both sheets are formatted as General. An

=IF(AND(N2="Australia")*COUNTIFS(Ratings!A$2:A$14,'Sheet 1'!AT2,Ratings!B$2:B$14,'Sheet 1'!AU2),TRUE,
IF(AND(N2="Brazil")*COUNTIFS(Ratings!D$2:D$13,'Sheet 1'!AT2,Ratings!E$2:E$13,'Sheet 1'!AU2),TRUE,
IF(AND(N2="CanadaEnglish")*COUNTIFS(Ratings!G$2:G$19,'Sheet 1'!AT2,Ratings!H$2:H$19,'Sheet 1'!AU2),TRUE,
IF(AND(N2="CanadaFrench")*COUNTIFS(Ratings!J$2:J$19,'Sheet 1'!AT2,Ratings!K$2:K$19,'Sheet 1'!AU2),TRUE,
IF(AND(N2="France")*COUNTIFS(Ratings!P$2:P$21,'Sheet 1'!AT2,Ratings!Q$2:Q$21,'Sheet 1'!AU2),TRUE,
IF(AND(N2="Germany")*COUNTIFS(Ratings!M$2:M$17,'Sheet 1'!AT2,Ratings!N$2:N$17,'Sheet 1'!AU2),TRUE,
IF(AND(N2="Italy")*COUNTIFS(Ratings!S$2:S$7,'Sheet 1'!AT2,Ratings!T$2:T$7,'Sheet 1'!AU2),TRUE,
IF(AND(N2="Mexico")*COUNTIFS(Ratings!V$2:V$17,'Sheet 1'!AT2,Ratings!W$2:W$17,'Sheet 1'!AU2),TRUE,
IF(AND(N2="Spain")*COUNTIFS(Ratings!Y$2:Y$24,'Sheet 1'!AT2,Ratings!Z$2:Z$24,'Sheet 1'!AU2),TRUE,
IF(AND(N2="United Kingdom")*COUNTIFS(Ratings!AB$2:AB$13,'Sheet 1'!AT2,Ratings!AC$2:AC$13,'Sheet 1'!AU2),TRUE,
IF(AND(N2="USA")*COUNTIFS(Ratings!AE$2:AE$20,'Sheet 1'!AT2,Ratings!AF$2:AF$20,'Sheet 1'!AU2),TRUE)))))))))))

Any thoughts?

And what are you trying to achieve by multiplying a boolean value by a number? — VBA Pete, Mar 02 '17 at 22:23
After searching many forums and sites, this seemed like the correct formula to use. I used the general structure in another project and it seems to work fine. So I just reapplied it to fit this scenario. The reason for the AND function was if a cell value meets condition 1, then use the countifs as condition 2. Certainly open to alternate suggestions — Scott, Mar 02 '17 at 22:31
So what exactly are you checking for in your `COUNTIF function`? — VBA Pete, Mar 02 '17 at 22:34
In sheet 1, I have values in 2 columns in the same row and need to get a TRUE/FALSE if they are "the same". There are multiple combinations of what could be considered the same. i.e. California = California / CA = California / Cali = CA So the countifs is comparing the 2 cells on sheet 1, to various columns on another sheet. And if the 2 cells on sheet 1 match a combination on the other sheet, it is true. — Scott, Mar 02 '17 at 22:39
To echo @VBAPete - What are you trying to accomplish? As you've probably noticed, dealing with super long formulas can get frustrating, if your eyes don't just pop out. Perhaps there's another way to accomplish what you're trying to do? This may be an [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) — BruceWayne, Mar 02 '17 at 22:41
I am still not following. Could you include sample data of your ratings sheet and what values do you enter in cells `AT2` and `AU2`? — VBA Pete, Mar 02 '17 at 22:52
The values in column `AT` and `AU` are already on the sheet when I receive it. I added screenshots of the data to OP. Hopefully that's acceptable — Scott, Mar 02 '17 at 23:14
The AND() is doing nothing as currently used. You could go through and delete the word AND everywhere it occurs, and nothing would change. Furthermore, at the end, you have no value_if_false in your last IF statement, so you are only getting FALSE if none of those statements evaluate as true, which mean either N2 does not ever equal any of your country names, or the COUNTIFS are returning zeroes. — Jacob Edmond, Mar 06 '17 at 20:05

score -1 · Accepted Answer · answered Oct 18 '19 at 06:14

you formula is working perfect there is nothing wrong in it.

Please check below sample what i have tried as like you. It's working for me and it returns True when actually the conditions are perfect.

Hope this is what your tried for, if not please elaborate your question. So that this will be solved.

And please check you formula option is Automatic or Manual.

Note: I Just copied you formula, and changed reference for Germany and it works perfect.

Sheet 1 as per your formula:

Sheet 1 My Own Written Formula as Like yours:

Ratings Sheet:

Excel Countifs formula

1 Answers1