Countif or Sumif

[philb99]

Need some help please

I have a tab with quite a few columns and need totals

Col A = Staff Names
Col B = Course Name
Col C = whether they have Pass or Fail
Col D = uk or non uk

i wish to creat a new tab and count how many staff have pass or fail and if they sit in Uk or non uk

New tab with reference to first tab
Col A = course Name
Col B + Col C = fail uk and fail non uk Totals
Col D + Col E = pass uk and pass non uk Totals

thanks

 

[Fluff]

How about something like
=COUNTIFS(Sheet1!A:A,A2,Sheet1!C:C,"Pass",Sheet1!D:D,"UK")

 

[kweaver]

With UNIQUE course names on the 2nd sheet in column A, what about:

Excel 2010
	A	B	C	D	E
1	Course	Fail UK	Fail Non	Pass UK	Pass Non
2	Math	0	0	1	1
3	English	0	0	2	0
4	Science	0	1	0	0
5	Biology	0	0	1	0
6	Trig	1	0	0	0
7	Literature	0	1	0	0
CourseCount

Cell Formulas
Range	Formula
B2	=COUNTIFS(Course!$B$2:$B$9,$A2,Course!$C$2:$C$9,"F",Course!$D$2:$D$9,"UK")
C2	=COUNTIFS(Course!$B$2:$B$9,$A2,Course!$C$2:$C$9,"F",Course!$D$2:$D$9,"Non")
D2	=COUNTIFS(Course!$B$2:$B$9,$A2,Course!$C$2:$C$9,"P",Course!$D$2:$D$9,"UK")
E2	=COUNTIFS(Course!$B$2:$B$9,$A2,Course!$C$2:$C$9,"P",Course!$D$2:$D$9,"Non")

 

[philb99]

Thanks - can I presume that Course! Is the name of the 1st tab - can you confirm do I expand the “F” to read Fail and “P” to read Pass please

Sorry I am very new to this kind of query

 

[philb99]

Just wondering how this looks at the Course Name am I missing something

thanks again

 

[kweaver]

Change "F" and "P" to whatever you're using to indicate Fail and Pass. Change "UK" and "Non" to whatever you're using.

And, yes, the sheet "Course" is where you have the staff (col A), Course (B), Pass/Fail (C), and UK/Non UK (D).

It's looking at the course name on the destination sheet where you need to have all of the UNIQUE course names in col A per the above example.
I assumed that the courses were fixed and that you could create a unique name list on the destination sheet.

 

[philb99]

Hi - that's perfect

Is there any way we can add another option in as in Column E of my Master tab = No Record (of registering).

I would like to report the volume of No Record under Columns B+C and D+E in the new tab - or would it be easier to set up a new table

Thanks again

 

[kweaver]

Is No Record being ADDED to B and C/D and E or is it listed like a course?
And, how are you indicating "no record" -- with a yes or no or an "x" if No Record?

 

[philb99]

Thanks for your reply.

Its a Column which has details of Enrollment - this can be either Enrolled, In-Progress,Pending or No Record

I wish to know how many Staff have - No Record reporting for all of the modules linked under the following Col B + Col C = fail uk and fail non uk Totals / Col D + Col E = pass uk and pass non uk Totals.

Appreciate the help

Would it be easier to create a New Table to try and design the formulae

 

[kweaver]

See if I understand this based on the info below. In the "Course" tab, I put a column E for Enrollment.

[TABLE="width: 448"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD]Staff[/TD]
[TD]Course[/TD]
[TD]Pass/Fail[/TD]
[TD]UK/Non UK[/TD]
[TD]Enrollment[/TD]
[/TR]
[TR]
[TD]Staff1[/TD]
[TD]Math[/TD]
[TD]P[/TD]
[TD]UK[/TD]
[TD]No Record[/TD]
[/TR]
[TR]
[TD]Staff2[/TD]
[TD]English[/TD]
[TD]P[/TD]
[TD]UK[/TD]
[TD]No Record[/TD]
[/TR]
[TR]
[TD]Staff3[/TD]
[TD]Science[/TD]
[TD]F[/TD]
[TD]Non[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]Staff4[/TD]
[TD]Biology[/TD]
[TD]P[/TD]
[TD]UK[/TD]
[TD]Enrolled[/TD]
[/TR]
[TR]
[TD]Staff5[/TD]
[TD]Trig[/TD]
[TD]F[/TD]
[TD]UK[/TD]
[TD]No Record[/TD]
[/TR]
[TR]
[TD]Staff6[/TD]
[TD]Literature[/TD]
[TD]F[/TD]
[TD]Non[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]Staff7[/TD]
[TD]Math[/TD]
[TD]P[/TD]
[TD]Non[/TD]
[TD]Enrolled[/TD]
[/TR]
[TR]
[TD]Staff8[/TD]
[TD]English[/TD]
[TD]P[/TD]
[TD]UK[/TD]
[TD]No Record[/TD]
[/TR]
</tbody>[/TABLE]


Excel 2010

	A	B	C	D	E	F	G	H	I
Course
Math
English
Science
Biology
Trig
Literature

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EEECE1]#EEECE1[/URL] , align: center"]Enrollment Count[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EEECE1]#EEECE1[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EEECE1]#EEECE1[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EEECE1]#EEECE1[/URL] , align: center"][/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: center"]Fail UK[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: center"]Fail Non UK[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: center"]Pass UK[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] , align: center"]Pass Non[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EEECE1]#EEECE1[/URL] , align: center"]Fail UK[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EEECE1]#EEECE1[/URL] , align: center"]Fail Non UK[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EEECE1]#EEECE1[/URL] , align: center"]Pass UK[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EEECE1]#EEECE1[/URL] , align: center"]Pass Non[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

</tbody>

CourseCount​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=COUNTIFS(Course!$B$2:$B$9,$A3,Course!$C$2:$C$9,"F",Course!$D$2:$D$9,"UK")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=COUNTIFS(Course!$B$2:$B$9,$A3,Course!$C$2:$C$9,"F",Course!$D$2:$D$9,"Non")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=COUNTIFS(Course!$B$2:$B$9,$A3,Course!$C$2:$C$9,"P",Course!$D$2:$D$9,"UK")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=COUNTIFS(Course!$B$2:$B$9,$A3,Course!$C$2:$C$9,"P",Course!$D$2:$D$9,"Non")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=COUNTIFS(Course!$B$2:$B$9,$A3,Course!$C$2:$C$9,"F",Course!$D$2:$D$9,"UK",Course!$E$2:$E$9,"No Record")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=COUNTIFS(Course!$B$2:$B$9,$A3,Course!$C$2:$C$9,"F",Course!$D$2:$D$9,"Non",Course!$E$2:$E$9,"No Record")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=COUNTIFS(Course!$B$2:$B$9,$A3,Course!$C$2:$C$9,"P",Course!$D$2:$D$9,"UK",Course!$E$2:$E$9,"No Record")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3[/TH]
[TD="align: left"]=COUNTIFS(Course!$B$2:$B$9,$A3,Course!$C$2:$C$9,"P",Course!$D$2:$D$9,"Non",Course!$E$2:$E$9,"No Record")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]