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


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


can someone briefly teache me? thx


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:

enter image description here

  • 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 – gamer0 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" – gamer0 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

To see if you have any duplicates in your range, use this expression:


It's going to work with a range of any length. For vertical ranges, drop the TRANSPOSE and use ROWS instead of COLUMNS for comparison.

| improve this answer | |
  • this solution would never cross my mind. thank you. – gamer0 May 13 '18 at 22:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.