0

I was trying to extract unique data from a list and I came across something strange. Below you'll find a screenshot with the translated formulas next to the cells (because my Excel isn't in English).

Excel screenshot

This is 3 times the same schedule, with only one change in cell B5.

Since COUNTIF($B$1:B4;$A$2:$A$9)=0 is a Boolean formula, this should return either TRUE or FALSE, no other options. So I changed it once to TRUE and once to FALSE.

But when I change it to TRUE, the result changes from h45 to h3, and when I change is to FALSE, the result changes to #DIV/0 (as expected).

How is it possible that TRUE returns h3 yet COUNTIF($B$1:B4;$A$2:$A$9)=0, which is TRUE, returns h45?

EDIT: I just noticed I forgot to change $A$2:$A$9 at the end of the formulas, but since the data isn't changed this really doesn't matter.

Pomonoli
  • 41
  • 4
  • 2
    `COUNTIF($B$1:B4;$A$2:$A$9)=0` doesn't return a _single_ `TRUE`. It returns a vertical array of `TRUE`/`FALSE` values. – ImaginaryHuman072889 Jul 19 '19 at 10:45
  • `COUNTIF($B$1:B4;$A$2:$A$9)` is not even the same size... – Mamoun Benghezal Jul 19 '19 at 10:47
  • 1
    @MamounBenghezal Doesn't need to be. As an array formula, this `COUNTIF` is counting the amount of times that `A2` is in range `B1:B4`, how many times `A3` is in range `B1:B4`... etc, and returning an array of these values. – ImaginaryHuman072889 Jul 19 '19 at 10:49
  • Ooooh an array of TRUE / FALSE values, I didn't think of that, thanks! – Pomonoli Jul 19 '19 at 10:49
  • 1
    This is something that may help for the unique values - https://stackoverflow.com/questions/1429899/getting-unique-values-in-excel-by-using-formulas-only – Vityata Jul 19 '19 at 10:51
  • Thanks, this formula worked though, so that's not a problem anymore. I just didn't really understand the formula so I tried to figure it out and that's how I stumbled upon this question. – Pomonoli Jul 19 '19 at 10:59

0 Answers0