AVERAGEIF With Multiple Criteria

[Coachcasa]

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"

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.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Stuck on AVERAGEIFS Problem
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Coachcasa]

I completely understand what you are saying about column A and I double checked every single row in my "column A" and has a regular number with no 0's or VALUE errors in them. Is there anything else that would create this error?

 

[Coachcasa]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Stuck on AVERAGEIFS Problem
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Oh, sorry about that. I REALLY appreciate our conversation here and didn't want to make anyone angry. Just thought I'd try and second source and see what I came up with. Thank you again for your help. New to all of this!

 

[Fluff]

Is there anything else that would create this error?

Not that I'm aware of, even if col A was text & not numbers you would get a #DIV/0! error

 

[Coachcasa]

Not that I'm aware of, even if col A was text & not numbers you would get a #DIV/0! error

I discovered an interesting riddle here in playing around with my formula. I am now only getting the #DIV/0 error and not the VALUE error I was getting-I don't know why. My column A is a calculated number. column B is text, column C is an entered number, and column D is text. I started playing around with my formula and decided to take out one of the reference columns and see what that did. When I take column B out of the formula it works and calculates as I want it to. I then experimented with taking out columns C and D one at a time and they still give me the #DIV/0 error. In other words when I calculated it with columns A, C, D it worked. When I calculated it with columns A, B, C it also worked When I do it with A, B, D I get the DIV/0 error. So this tells me there is something about using both column B and D together in the formula that is preventing it from working. It's as if the formula doesn't like two text columns in there. Thoughts on this?

 

[Fluff]

You will get a Div/0 error if nothing matches the criteria, so maybe col B has leading/trailing spaces.

 

[Coachcasa]

You will get a Div/0 error if nothing matches the criteria, so maybe col B has leading/trailing spaces.

You replied before I could update my previous comment. Now I'm getting a different test result: when I calculated it with columns A, C, D it worked. When I calculated it with columns A, B, C it also worked When I do it with A, B, D I get the DIV/0 error. So this tells me there is something about using both column B and D together in the formula that is preventing it from working. It's as if the formula doesn't like two text columns in there.

 

[Fluff]

There is nothing wrong with the formula you originally posted, it works fine. The problem is in your data.

 

[Coachcasa]

There is nothing wrong with the formula you originally posted, it works fine. The problem is in your data.

Data indeed. This seems to get weirder. With further experimentation, I've now discovered that if I use the two text columns B and D together I'm fine. If I use the number column in column C only I'm fine. When I combine C with either or both B and D together is when I get the DIV/0 error. So it doesn't seem to like that I'm combing numbers and text together. Does that make any sense to you? I tried an experiment and gave numbers to represent the data in in columns B and D and did the formula that way, but still got the DIV/0 error.

 

[Fluff]

Does that make any sense to you?

Nope

+Fluff 1.xlsm
	A	B	C	D	E	F
1	Score	Difficulty	Weather	Location
2	50	Hard	Sunny	Home	5
3	#VALUE!	Easy	Rainy	Work	a
4	70	Moderate	Cloudy	School	7	80
5	60	Hard	3	Work	6
6	100	hard	3	Work	10
7		hard	3	Work
8	200		1	Work	20
9
Main

Cell Formulas
Range	Formula
F4	F4	=AVERAGEIFS($A$2:$A$100,$B$2:$B$100,"Hard",$C$2:$C$100,3,$D$2:$D$100,"Work")
A2:A8	A2	=IF(E2="","",10*E2)