Get Number of Session using COUNTIFS()

[Abdulrahman7]

Good morning Everyone,

IF image doesn't appear please click on this link
http://imgur.com/a/78TVB


I'm working on Training list report, as you see in the above image I have created training list table, which contain EmpID, Course title, sessions,noOfHours and Attendance.


in Report 1 I need to get number of session for each course title since, each session could have more than employee ID(repeated session number), to avoid repeated session I have inserted new column I named it [Mrg CourseTitle], I Count this column and it worked correctly, I got the result I wanted, I need best way to count the number of sessions instead of inserting new column, something like count only one row of duplicated data.


Thank you in Advance

 

[jayalaxmi]

Good morning Everyone,

IF image doesn't appear please click on this link
http://imgur.com/a/78TVB


I'm working on Training list report, as you see in the above image I have created training list table, which contain EmpID, Course title, sessions,noOfHours and Attendance.


in Report 1 I need to get number of session for each course title since, each session could have more than employee ID(repeated session number), to avoid repeated session I have inserted new column I named it [Mrg CourseTitle], I Count this column and it worked correctly, I got the result I wanted, I need best way to count the number of sessions instead of inserting new column, something like count only one row of duplicated data.


Thank you in Advance

Hi Rahman

Good noon

Can you please share the template file as the image and the link is not working out for me at my place..

Regards
Jaya

 

[Abdulrahman7]

Thank you for replying Mr. Jayalaxmi

Yes here it is

 

[bosco_yip]

...... I need best way to count the number of sessions instead of inserting new column, something like count only one row of duplicated data.............

In J7, formula copy right to K7 :

=SUMPRODUCT(($C$2:$C$24=J$6)/COUNTIFS($C$2:$C$24,$C$2:$C$24,$D$2:$D$24,$D$2:$D$24))

Regards
Bosco

 

[Abdulrahman7]

In J7, formula copy right to K7 :

=SUMPRODUCT(($C$2:$C$24=J$6)/COUNTIFS($C$2:$C$24,$C$2:$C$24,$D$2:$D$24,$D$2:$D$24))

Regards
Bosco

Thanks that's really helped me I mean exactly what I need, could you explain the criteria of COUNTIFS, you put the range same like the criteria how is that.

Appreciate your assistance

 

[bosco_yip]

Thanks that's really helped me I mean exactly what I need, could you explain the criteria of COUNTIFS, you put the range same like the criteria how is that.

Appreciate your assistance

=COUNTIFS($C$2:$C$24,$C$2:$C$24,$D$2:$D$24,$D$2:$D$24)

COUNTIFS(range1,criteria1,range2,criteria2)

counts the number of cells within a range that meet the given 2 conditions.

COUNTIFS(List1,List1,List2, List2)

becomes

{"Oracle1";"Oracle1";"Oracle1";"Oracle2";"Oracle2";"Oracle2";"Oracle2";"SAP1";"SAP1";"SAP1";"SAP2";"SAP2";"Sap3";"Sap3";"Oracle3";"Oracle3";"Oracle3";"Oracle3";"Oracle3";"Oracle3";"Oracle3";"SAP4";"SAP4"}

and returns

{3;3;3;4;4;4;4;3;3;3;2;2;2;2;7;7;7;7;7;7;7;2;2}

Oracle1 - 3 (Oracle1 exists 3 times in range List)

Oracle2 - 4 (Oracle2 exists 4 times in range List)

SAP1 - 3 (SAP1 exists 3 times in range List)

SAP2 - 2 (SAP2 exists 2 times in range List)

Sap3 - 2 (Sap3 exists 2 times in range List)

Oracle3 - 7 (Oracle3 exists 7 times in range List)

SAP4 - 2 (SAP4 exists 2 times in range List)

Regards
Bosco

 

[Abdulrahman7]

You're amazing

=COUNTIFS($C$2:$C$24,$C$2:$C$24,$D$2:$D$24,$D$2:$D$24)

COUNTIFS(range1,criteria1,range2,criteria2)

counts the number of cells within a range that meet the given 2 conditions.

COUNTIFS(List1,List1,List2, List2)

becomes

{"Oracle1";"Oracle1";"Oracle1";"Oracle2";"Oracle2";"Oracle2";"Oracle2";"SAP1";"SAP1";"SAP1";"SAP2";"SAP2";"Sap3";"Sap3";"Oracle3";"Oracle3";"Oracle3";"Oracle3";"Oracle3";"Oracle3";"Oracle3";"SAP4";"SAP4"}

and returns

{3;3;3;4;4;4;4;3;3;3;2;2;2;2;7;7;7;7;7;7;7;2;2}

Oracle1 - 3 (Oracle1 exists 3 times in range List)

Oracle2 - 4 (Oracle2 exists 4 times in range List)

SAP1 - 3 (SAP1 exists 3 times in range List)

SAP2 - 2 (SAP2 exists 2 times in range List)

Sap3 - 2 (Sap3 exists 2 times in range List)

Oracle3 - 7 (Oracle3 exists 7 times in range List)

SAP4 - 2 (SAP4 exists 2 times in range List)

Regards
Bosco

Thanks Bosco for clarifying you're amazing :D