Countif or Sumif

philb99

Active Member
Joined
Feb 3, 2014
Messages
385
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
How about something like
=COUNTIFS(Sheet1!A:A,A2,Sheet1!C:C,"Pass",Sheet1!D:D,"UK")
 
Upvote 0
With UNIQUE course names on the 2nd sheet in column A, what about:


Excel 2010
ABCDE
1CourseFail UKFail NonPass UKPass Non
2Math0011
3English0020
4Science0100
5Biology0010
6Trig1000
7Literature0100
CourseCount
Cell Formulas
RangeFormula
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")
 
Upvote 0
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
 
Upvote 0
Just wondering how this looks at the Course Name am I missing something

thanks again
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
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
 
Upvote 0
See if I understand this based on the info below. In the "Course" tab, I put a column E for Enrollment.

StaffCoursePass/FailUK/Non UKEnrollment
Staff1MathPUKNo Record
Staff2EnglishPUKNo Record
Staff3ScienceFNonPending
Staff4BiologyPUKEnrolled
Staff5TrigFUKNo Record
Staff6LiteratureFNonPending
Staff7MathPNonEnrolled
Staff8EnglishPUKNo Record

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>


Excel 2010
ABCDEFGHI
1Enrollment Count
2CourseFail UKFail Non UKPass UKPass NonFail UKFail Non UKPass UKPass Non
3Math00110010
4English00200020
5Science01000000
6Biology00100000
7Trig10001000
8Literature01000000

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
CourseCount

Worksheet Formulas
CellFormula
B3=COUNTIFS(Course!$B$2:$B$9,$A3,Course!$C$2:$C$9,"F",Course!$D$2:$D$9,"UK")
C3=COUNTIFS(Course!$B$2:$B$9,$A3,Course!$C$2:$C$9,"F",Course!$D$2:$D$9,"Non")
D3=COUNTIFS(Course!$B$2:$B$9,$A3,Course!$C$2:$C$9,"P",Course!$D$2:$D$9,"UK")
E3=COUNTIFS(Course!$B$2:$B$9,$A3,Course!$C$2:$C$9,"P",Course!$D$2:$D$9,"Non")
F3=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")
G3=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")
H3=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")
I3=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")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top