WK wise connect not connect count of agent

[Suresh Badhani]

There i have data where a agent call multiple time in a call and status connect and not connect then we convert date to week wise as wk1,wk2,wk3,wk4
means dial status1then dial status 2 then dial status 3 than dial status 4 by multiple agents

Here I want total Connect ,Not connect status of wk1 for agents I try many types of countifs but not matching with data ,means how many count in wk1 connect and not connect similarly wk 2 wk3 I wil do . Please help i not success

WK1

WK2

WK3

WK4

Agent Name

Total Dialeld

Connect

Not Connect

Agent Name

Total Dialeld

Connect

Not Connect

Agent Name

Total Dialeld

Connect

Not Connect

Agent Name

Total Dialeld

Connect

Not Connect

A

0

A

0

A

0

A

0

B

0

B

0

B

0

B

0

C

0

C

0

C

0

C

0

 

[bosco_yip]

Here is my formula solution in re-structure your "Summ" sheet table layout

And

In C5, formula copied across right to J5, and all copied down :

=IFERROR(SUMPRODUCT((data!$F$2:$F$46=$A5)*(INDEX(data!$G$2:$J$46,0,MATCH(C$2,data!$G$1:$J$1,0))=C$1)*(INDEX(data!$B$2:$E$46,0,MATCH(C$3,data!$B$1:$E$1,0))=C$4)),0)

Then

Select C5:J7, copy& paste to N5:U7, Y5:AF7 and AJ5:AQ7

Remark :

1. All "Summ" sheet criteria header must as same as the "data" (source data) sheet description.

2. All "Summ" sheet criteria header should all put in the same column (e.g. C1:C4)

3. Avoid left blank inside the criteria cells

 

[Suresh Badhani]

Thanks ❤ but Very tough to understand also nos not matching.if any other solution please help

 

[p45cal]

In the attached, choose your WK in cell B9 of the summ sheet. Then the most important question: are the values correct?
If so, great!
The data in that pivot table actually comes from the table on sheet data (2), which in turn has come from the data on sheet data, however your arrangement of data on that data sheet makes it difficult to extract summary information. So I would recommend the data to be as on sheet data (2) from the outset and never be in the format on the data sheet.
[Maybe you can tell me the raw data comes in another form? If so it will probably be easier to to summarise directly from that data.]

By the way,

then we convert date to week wise

I would recommend NOT doing this conversion but leaving it as a date; the pivot table can can group dates into weeks.