# how to save time with multiple COUNTIFS possibilities?

I am sure that there is a smarter approach then this brute one:

``````=IF(COUNTIFS(O40<>P40;O40<>Q40;O40<>R40;O40<>S40;O40<>T40;O40<>U40;
p40<>o40;p40<>Q40;p40<>R40;p40<>S40;p40<>T40;p40<>U40;
q40<>P40;q40<>o40;q40<>R40;q40<>S40;q40<>T40;q40<>U40;
r40<>P40;r40<>Q40;r40<>o40;r40<>S40;r40<>T40;r40<>U40;
s40<>P40;s40<>Q40;s40<>R40;s40<>o40;s40<>T40;s40<>U40;
t40<>P40;t40<>Q40;t40<>R40;t40<>S40;t40<>o40;t40<>U40;
u40<>P40;u40<>Q40;u40<>R40;u40<>S40;u40<>T40;u40<>o40);1;2)
``````

I tried something like this but its not working as that above:

``````=IF(COUNTIF({O40;P40;Q40;R40;S40;T40;U40}<>{O40;P40;Q40;R40;S40;T40;U40};{O40;P40;Q40;R40;S40;T40;U40}<>{O40;P40;Q40;R40;S40;T40;U40});1;2)
``````

can someone briefly teache me? thx

EDIT:

explanatory: on row 40 I have 7 cells with some mixed values (numbers, laters). what I'm trying to do here is to check every cell from those 7 with every cell of those 7. so if there is no match between them then value 1 is displayed and if there is a match then value 2 displays. embeding image with colors:

• You seem to be using COUNTIFS wrong. The first argument is normally a range (perhaps O40:U40?), and the list of conditions start from the 2nd one. It would also help if you wrote about the problem that you want to solve, I'm having a hard time imagining what such a convoluted COUNTIFS calculation could represent. Perhaps there is another way to solve your business problem. – ttarchala May 13 '18 at 21:08
• @ttarchala I made an edit... yeah, I'm kinda sense that I use COUNTIF wrongly but that first formula (the long one) works flawlessly so far in my testings – user902300 May 13 '18 at 21:16
• The formula might be giving you correct results purely by accident. Normally the 1st, 3rd, 5th etc. every odd argument to COUNTIFs must be a range, so I'm guessing the function quietly converts the results from comparison into ad-hoc ranges there. It would help if you didn't just post the formula but also test data which you are comparing and even explained the "business" meaning of these data. – ttarchala May 13 '18 at 21:29
• @ttarchala so I should double it? like: =IF(COUNTIF(O40<>P40;O40<>P40 ; O40<>Q40;O40<>Q40...... ? well as I explained in the post: all I wana is to compare those 7 cells with each others and find out if there is not any "duplicates" – user902300 May 13 '18 at 21:44
• For finding duplicates, it might be much more useful to apply the function UNIQUE. Try calculating =COUNT(UNIQUE(O40:U40)) and see what it yields for various test data. – ttarchala May 13 '18 at 21:46

``````=IF(ROWS(UNIQUE(TRANSPOSE(A1:G1))) = COLUMNS(A1:G1); "UNIQUE"; "DUPLICATE")