Very useful excel function: TÆL.HVIS (CountIf)
19 September, 2009 – 04:16This is the function that is worth of knowing as soon as possible.
This function was the solution for most of the queries for help arriving to me, as I said this is a very useful function, use as often you can! 
For the ones that are just now getting to know the TÆL.HVIS function:
This excel function sums all those fields that are satisfying the given condition.
In the english version it is called as CountIF, that could be translated as on danish…. .
Usage: TÆL.HVIS( range ; condition )
This will become interesting, when we look at it through practical examples..
1. How do we know, if the new lines are already incorporated in our list?
The known method is, that by each line there is a counter, how many times is there the item in the list. Perhaps this was the reason for the invention of this function.
The problem with the above is that though it can be seen, which item is there twice or more in the list, but the process of filtering the items out of the list is still remains a manual task.
And here comes the trick:
The solution is rather simple to this: lets find out, which instance is which in the list of similars (first, second, third, etc). Thus we can see, which is the “original”, and which are the “copies”. Following it is simple to filter, or sort the periodicals – thus can be removed – while the originals may remain intact.
As a reference, about the cell-references, I’d recommend you the post (absolute, relative and mixed cell-references in excel), submitted to this blog, bit earlier.
2. Developing the order with the function
With the method bellow, we will get a list where there will be items with numbers/postitions repeating, being in a tie.
The next trick:
Taking the position in count – in a tie:
The excel file with the examples, can be downloaded from here ( also with an extra example, that is not shown here): http://www.medeffektivitet.dk/downloads/tael.hvis.xls
In the future, there will be more data on the TÆL.HVIS function, as the most important things are still behind.… so, don’t miss it – subscribe now!
