4

I have some data in Excel 365 for Mac that is formatted like so:

USER    GROUP    IN_SCOPE
1       GroupA   TRUE
2       GroupA   TRUE 
3       GroupA   FALSE
4       GroupB   TRUE
5       GroupB   FALSE

I would like to create a pivot table that contains the following:

GROUP   COUNT_IN_SCOPE
GroupA  2
GroupB  1

I believe this should be possible to do within the pivot table itself, but most solutions online seem to point to using Power Query. Unfortunately that's not available for Mac so that's out. I've also tried several calculated fields, including 'IN_SCOPE'*1 and IF('IN_SCOPE', 1, 0) but both return 0 for all rows.

A few notes on the choice of approach: I prefer doing this as a pivot table rather than something else so that I can expand/collapse other meta-categories present in my data. To maintain legibility in the raw data, I would strongly prefer to leave my IN_SCOPE column formatted as Boolean values and I would strongly prefer not to add additional columns.

rs2345
  • 41

2 Answers2

3

One way is to add another column maybe called value with a formula such as =IF([@[in score]],1,0) then pivot with in_scope as a column and value in the value section. You can then filter out the falseenter image description here

gns100
  • 1,261
1

I see your question is old, but I stumbled on the same issue today and found my way out. I think the secret is to set your field twice. Based on your data (and no extra column or formula), I would suggest setting the following fields:

  • IN_SCOPE as Filters, and you select "TRUE"
  • GROUP as Rows
  • IN_SCOPE as Values (use Count)

And I get exactly what you expect

cl1112
  • 11