pivot table count based on criteria

[swofm]

Hi Everyone,
I have a table where it shows logs of different users working on a claim. I want to be able to create a pivot table but the count on the "Status" field should satisfy two conditions.

For this scenario, I want to count the "Pending" status for User1. For User1 to have a count, User1 has to have both "Pending" and "Ready" status. So for claim# 123, User1 will have 1 count but not for claim# 456 because User2 had the Ready status. I hope I have explained it clearly. Thank you in advance.

Claim#	Status	Username	Date
123	Pending	User1	4/1/18
123	Ready	User1	4/2/18
123	Archived	User2	4/4/18
456	Pending	User1	4/10/18
456	Ready	User2	4/11/18
456	Archived	User2	4/13/18

<tbody>
</tbody>

 

[SpillerBD]

Build Pivot Table.
Status for Columns
Claim# for Rows
Username for Rows (2nd position)
Count of Claims in Values

Set to Tabular mode under Design
Turn off Grand Totals and Subtotals

Filter Status to NOT show "Archived"
Right Click a User name to select the Filter, Values, Count of Claim Greater or equal to 2.

Turn Grandtotals back on.

 

[swofm]

thank you, but how do i summarize to show total counts grouped per user?

 

[SpillerBD]

For any fields to subtotal, Right click on a label and choose Subtotal.