Hi everyone. I didn't find an answer to this question elsewhere, so I thought I'd ask and see if anyone can help me. I am trying to create a formula using AVERAGEIFS that is pulling criteria from three different columns and then giving me an average score from a fourth column if all of the other three columns meet the criteria. I can't get this to work and perhaps you can't do this or perhaps AVERAGEIF formula isn't suited to this. To give you a better feel for what I'm doing I'll make up a simplified version here with random imaginary criteria so I don't have to share everything with you. I want to get the average score from column A, but only when it meets a specific combination of criteria from columns B, C, and D at the same time. So for example, I want to know what the average score is if Column B registers "Hard", Column C registers "Sunny", and Column D registers "Work"
Here is how I'm trying to build my formula, but it only results in #VALUE!
=AVERAGEIFS($A$2:$A$5,$B$2:$B$5,"Hard",$C$2:$C$5,"Sunny",$D$2:$D$5,"Work")
Please help me and tell me what I'm doing wrong or if this just won't work for what I want. I looked online at some different examples and put this together based on what I was seeing. However, either I'm missing something simple or am way off! Thanks for your help.
Column A | Column B | Column C | Column D |
Score | Difficulty | Weather | Location |
50 | Hard | Sunny | Home |
80 | Easy | Rainy | Work |
70 | Moderate | Cloudy | School |
60 | Hard | Sunny | Work |
Here is how I'm trying to build my formula, but it only results in #VALUE!
=AVERAGEIFS($A$2:$A$5,$B$2:$B$5,"Hard",$C$2:$C$5,"Sunny",$D$2:$D$5,"Work")
Please help me and tell me what I'm doing wrong or if this just won't work for what I want. I looked online at some different examples and put this together based on what I was seeing. However, either I'm missing something simple or am way off! Thanks for your help.