Forum Discussion
Insert COUNTA as a calculated field (pivot report)
Hello, I need help to insert a calculated field in a pivot report where the field counts notblank cells (see Number of yrs not blank). The point is to know for each donor how many years they have given. I am new to calculated field and I tried inserting COUNTA(b2:d2) in the formula field and it won't accept it. Thanks!
Must you use a pivot table for this? If so, easiest to add the data to use Power Pivot (PP). That gives you the option to perform a 'distinct count' of the Years. Oh, but this will not work on a Mac as PP is not supported on that platform.
If you have a modern Excel version you can use a formula like:
=COUNT(UNIQUE(FILTER(Table1[Year],Table1[Donor]=A1)))
where A1 refers to a cell with a donor name from your table.
- Thank you. My original list is a vertical list of 42,000 name, date, gift hence the pivot.
Elva_Tanguerre Then I would go for the Power Pivot solution.
