Calculate formula using multiple worksheets

[Sherylb]

Hello,

I'm new to this site so I do apologise if I have duplicated threads.

From the data on my spreadsheet I want to calculate how many students who received a final grade of 5, were 1st year or 2nd year or 3rd year etc. And I've drawn a blank as to where to start!

I have one worksheet with all data on it, eg. names, courses, results. But I have created another worksheet (#2) purely for the stat side of things. So on my Stat workbook (#2) I want to pull the data from worksheet #1.

As a general idea this is what my workbook #1 looks like:

Name, Yr Level, Course, Grade (column headings)

Joe Bloggs, 2, B Engineering, 6
Meg Ryan, 1, B Arts, 5
Tom Hanks, 3, B Communication, 5

I've worked out a formula to calculate how many students in total received what grade by using the "COUNTIF" formula.

Any suggestions on how I can break this number down further and see how many students per year level received what grade?

Thanks
Sheryl

 

[Ana Wisbeck]

Hi Sheryl,
Here's a simple solution for you. You can use =countifs() function.


=COUNTIFS(D1:D3,"=1",F1:F3,"=5") where D1 to D3 has the level and F1 and F3 has the grades.


In order to get the referencing right, copy and paste my formula in your "stats" sheet, now delete my D1:D3 range from the formula bar and now click on the data sheet and select the range of cells and excel should take care of the referencing for you.

Ana

 

[Sherylb]

Hi Ana,

Thanks for that, that's a great help!

Also, the way I've set up my spreadsheet lists the courses each student has studied one under each other. eg.

TEST1001
TEST1002
TEST1003

I've put the year level (in it's own column) on the first line along with TEST1001. Am I right in thinking that the formula will only look for and return what is on that first line? So it will look and see if there's a yr level 1 and then go across to the grade and match it with whichever grade I've told it to look for?

If so, if I merge the yr level cells together instead of listing 1, 1, 1 against each individual course will it count this as one cell? Or will I need to list the yr level on each line?

Thanks
Sheryl

 

[Ana Wisbeck]

Hi Sheryl,

Glad that helped!

You are correct, if you merge the cells, the condition only apply for the cell that is directly across. So for example if you have D1 to D3 merged and only 1 showing for all three cells it will only count one cell if F1 is equal to 5.

Unfortunatelly I don't have a clever solution for you this time, I can only think of repeating the value in the Level column which can be quite tedious if you have a large number of students in your list. Maybe give it a day or two to see if someone else can think of a way around this?

Good luck!

Ana