Post History

*Perhaps* (if a process of several relatively familiar steps is easy to remember) but additional formulae may be appropriate, a little extension to the source data required and some formatting. However, the offered solution only works for at most three states (here `YES`, `NO`, and `X`).

First, add the required additional data. This is the trigger for the `X`s, so perhaps a formula in A7 and copied across to B7 of:

    =unique($A2:$A7)

Enter `0` in ColumnD of each of the rows populated by the [UNIQUE](https://support.google.com/docs/answer/3093198) formula.
In D2 and copied down (say by double-clicking the fill handle):

    =IF(C2="YES",1,-1)

Then in say F2:

    =query(A2:D10,"select A, sum(D) group by A pivot B")

Finally for presentation purposes select from ColumnF and to the right and Format > Number > More Formats > Custom number format and:

    "YES";"NO";"X"

and <kbd>Apply</kbd>.

![Inputs and output](https://software.codidact.com/uploads/BKSgo4fJtudJYQaPtpg1BF39)

This is extensible for more people (possibly most easily by inserting rows at the position of the UNIQUE formula) but would then require additional population of ColumnD and extending the [Query](https://developers.google.com/chart/interactive/docs/querylanguage) range.

The process would be much simpler if a fill (could be applied with Conditional Formatting) was allowed instead of the `X`s and would be easier to extend if a blank row were allowed in the pivot table under its headings.