Calculated Field Custom Formula not working in Google Sheets Pivot Table

Question

Here's a dummy spreadsheet of data and a pivot table

I've got data in column A (Person) and column B (CheckedIn).

People have CheckedIn values of between 0 and 6.

I want a Calculated Field to show a number beside each person that is reflective of their CheckedIn value:

• if 0 then 0 Check-ins
• if 1 then 1 Check-in
• if 2 then 2 Check-ins
• if 3 then 3 Check-ins
• if 4 then 4+ Check-ins
• if 5 or more, then 4+

(so I can then create a pie chart from the calculated field column)

For my Calculated Field Custom Formula I'm using:

=IF(CheckedIn = 0, "0 Check-ins", IF(CheckedIn = 1, "1 Check-in", IF(CheckedIn = 2, "2 Check-ins", IF(CheckedIn = 3, "3 Check-ins", "4+ Check-ins"))))

Summarise by is set to Custom.

Unfortunately, this is only giving me 0 for anyone with a 0 (which is right) or 4+ Check-ins for everyone else (regardless of how many check ins they have, which is wrong):

Can anyone see what I'm doing wrong?

Answer 1

You may try:

=switch(counta(CheckedIn),0,"0 Check-ins",1,"1 Check-in",2,"2 Check-ins",3,"3 Check-ins","4+ Check-ins")