Question 1 came from a recent problem I had when I completed the valuation for all of the properties, and an "across the board” assumption changed.Īfter all properties are valued I then create a Summary Table that is linked to each workbook. The valuation principals are the same for all properties, but property characteristics are different. I am working with a valuation template I created to value a portfolio of properties. Here is a little bit of background on what I am trying to do.
Here is a little bit of background info on what I am trying to do. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers.Thanks for the help. When contacting me, be as specific as possible: For instance, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I answer readers' questions when I can, but there's no guarantee. Then, we used an expression to return the number of unique values in the same range. In this case, we used a function and two built-in features to count the number of times a value is repeated in the same range. Within the context of duplicates, definitions aren't the same. If you can't, subtract 1 from the final count, as shown in Figure G.įigure G Refining the expression. If you can delete the header text, this expression works. There's nothing wrong, but there are actually 4 unique values in column B, because the function evaluates the entire column - including the string Species in B1. sort of.įigure F Return the number of unique values in a column. =SUMPRODUCT((range"")/COUNTIF(range,range&""))įigure F shows this function at work in our example data. When counting unique values, use the following expression:
To the best of my knowledge, Excel still doesn't have a built-in function for counting unique values. This solution has been around for a long time, and I can't take credit for it. The traditional method is to use the SUMPRODUCT() function. The flip side of counting duplicates might be to count the number of unique values. In this case, it also displays an optional grand total for the column ( Figure E).įigure E There's now a subtotaling row below each group. This feature will insert a subtotaling row below each group. In this case, select the Species field and specify the Count function ( Figure D).
However, it's not as convenient, because you'll have to sort the data first. One of the first two solutions will probably satisfy most situations, but you could also use Excel's Subtotal feature, which evaluates data by groups.
The COUNTIF() is adequate, but you might want a list of unique values rather than the full dataset. If you enter one of the values below to the dataset, the function will add it to the count, as shown in Figure B, even if it's not in the actual data range.įigure B The reference evaluates non-contiguous values. It's great if you add and delete records, but it'll also evaluate non-contiguous values. The main problem with this structure is the B:B reference. If we were counting strictly duplicates, we wouldn't include the first occurrence of the value.Īs you can see, the function returns the correct count, and it's a quick fix. We're not counting the number of actual duplicates but rather the number of times the value occurs within the given range. Figure A COUNTIF() counts duplicate species.