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. In the PivotTable Fields list (to the right), drag the Species field to the Row Labels and the Values sections.įigure C Dragging the same to both sections forces a unique count.Accept all the default values in the resulting dialog - simply click OK.Click the Insert tab and then click PivotTable in the Tables group.Click a Species value (any cell in B2:B5).In this case, you can use a PivotTable, as shown in Figure C. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |