Average of cells that meet two criteria

[tjack_87]

Hi everyone.

i have a sharepoint list that contains observations my training team have made for numerous colleagues in the department. The can be observed on various different tasks and they could have been observed more than once. I ask my team to rate the colleague from 1 to 4 on that task for each observation

What i want to do is have an excel sheet that looks at that sharepoint list (or it's exported version, which i think is a query file in excel) and calculate the average rating given if the name in Column A matches 'Bob' AND the task in Column B matches 'Task1'

People have mentioned there may be addins that could be utilised to do this, but as this is a work computer, my company will not allow me to use these.

Is this possible to do? any help would be appreciated.

 

[steve the fish]

Excel has a function called AVERAGEIFS which you can use for averaging based on criteria.

 

[kensandy1]

If I understand correctly what you are asking, the Index and Match functions can be paired to search based on two criteria. For example:

{=INDEX($F$1:$AM$2000,MATCH(1,('MySheet'!$F$1:$F$2000=$A5)*('MySheet'!$G$1:$G$2000=$C$1),0),3)}

This look for the value A5 (on the active sheet) in column F1 through F2000 (on MySheet) and value C1(on the active sheet) in column G1 through G2000 (on MySheet).

In order for this function to work, after you are done typing it, hold the Ctrl+Shift key and press enter. That will add the {} to the function.

 

[tjack_87]

Excel has a function called AVERAGEIFS which you can use for averaging based on criteria.

Hi,

i tried this but got a Div# error. what could i be doing wrong?

this is the formula i used:

=AVERAGEIFS([Book1]P9!$F:$F,[Book1]P9!$A:$A,A2,[Book1]P9!$E:$E,B1)

FYI - [Book1]P9! is the name of the exported Sharepoint spreadsheet, Column F has all the ratings for everyone, Column A has everyone's name and Column E has the tasks

So essentially average out the ratings in column F of [Book1]P9, for the colleague in A2 AND the task in B1 of my active sheet

Hope that makes sense

 

[tjack_87]

If I understand correctly what you are asking, the Index and Match functions can be paired to search based on two criteria. For example:

{=INDEX($F$1:$AM$2000,MATCH(1,('MySheet'!$F$1:$F$2000=$A5)*('MySheet'!$G$1:$G$2000=$C$1),0),3)}

This look for the value A5 (on the active sheet) in column F1 through F2000 (on MySheet) and value C1(on the active sheet) in column G1 through G2000 (on MySheet).

In order for this function to work, after you are done typing it, hold the Ctrl+Shift key and press enter. That will add the {} to the function.

Thanks for this, can i just ask what data is in those cells and columns in your example?
for me, my active sheet has the colleague's name in A2 and the Task in B1. on the sharepoint spreadsheet, the list of names are in Column A, the Tasks in Column E and the ratings in F

the formula i want to put into B2 of my active sheet needs to say look at the list of names in Column A and and the list of tasks in Column E and pick out the ones that match BOTH the name in A2 of my active sheet and the task in B1 of my sheet. Then, average out the ratings given.

 

[kensandy1]

A5 contains a persons name and C1 contains a date so it looks through columns F1 and G1 on MySheet and finds the row where it they are the same and pulls data from the third column (the ,3 at the end says which column to pull from)

Also, correction the the formula I posted:

{=INDEX('MySheet'!$F$1:$AM$2000,MATCH(1,('MySheet'!$F$1:$F$2000=$A5)*('MySheet'!$G$1:$G$2000=$C$1),0),3)}

so it looks at the table $F$1:$AM$2000 and pulls the third column from F which ends up being H (count the first column, so F, G, H) of that table.

 

[kensandy1]

{=INDEX('SharePointSheet'!$A$1:$F$2000,MATCH(1,('SharePointSheet'!$A$1:$A$2000=$A$2)*("SharePointSheet'!$E$1:$E$2000=$B$1),0),6)}

I think this will work, but not 100% sure without seeing the sheet. Substitute the name of the worksheet for SharePointSheet.

 

[tjack_87]

{=INDEX('SharePointSheet'!$A$1:$F$2000,MATCH(1,('SharePointSheet'!$A$1:$A$2000=$A$2)*("SharePointSheet'!$E$1:$E$2000=$B$1),0),6)}

I think this will work, but not 100% sure without seeing the sheet. Substitute the name of the worksheet for SharePointSheet.

Hi, i have tried this and changed the 'sharepoint list' names to the name of the file, but getting an error. I have some replica tables here if that helps?

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Customer Service[/TD]
[TD]Absence Policy[/TD]
[/TR]
[TR]
[TD]Bob Smith[/TD]
[TD]Formula Here
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sue Wiliiams[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And below is the root spreadsheet containing the data

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]othercolumn[/TD]
[TD]other column[/TD]
[TD]other column[/TD]
[TD]task[/TD]
[TD]rating[/TD]
[/TR]
[TR]
[TD]Bob Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Absence Policy[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Sue Wiliiams[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Customer Service[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Sue Wiliiams[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Absence Policy[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Sue Williams[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Customer Service[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Bob Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Customer Service[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Bob Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Customer Service[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Bob Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Absence Policy[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Sue Williams[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Absence Policy[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Sue Williams[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Absence Policy[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


the idea is once i have the formula sorted, i can drag it so it works for the other colleagues/tasks