CODEWITHSUNDEEP
excelvba
SQLMike
SQLMike

Reputation: 57

Excel Pivot (Consolidate data based on the open and closed date)

we have 4 date columns(created year/month. closed year/month). The tricky part in the output is the closed count.

In the output, the columns(year,Month) are based on (created year/month) so when i pivot the source data value the closed ticket count should be based on the columns(closed year/month).

Thanks In Advance

enter image description here enter image description here

Upvotes: 1

Views: 2125

Answers (1)

teylyn
teylyn

Reputation: 35915

I don't think you can do that kind of calculation with a pivot table. But you can use a regular table and Countifs to get the result you describe.

enter image description here

The formula in D17 is

=COUNTIFS(Table1[Created Year],B17,Table1[Created Month],C17)

In E17

=COUNTIFS(Table1[status],"closed",Table1[Closed Year],B17,Table1[closed month],C17)

Copy down

Upvotes: 1

Related Questions