AVERAGEIF With Multiple Criteria

Coachcasa

New Member
Joined
Feb 1, 2021
Messages
18
Office Version
  1. 2016
Platform
  1. MacOS
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 AColumn BColumn CColumn D
ScoreDifficultyWeatherLocation
50HardSunnyHome
80EasyRainyWork
70ModerateCloudySchool
60HardSunnyWork

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Coachcasa

New Member
Joined
Feb 1, 2021
Messages
18
Office Version
  1. 2016
Platform
  1. MacOS
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

New Member
Joined
Feb 1, 2021
Messages
18
Office Version
  1. 2016
Platform
  1. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 1, 2021
Messages
18
Office Version
  1. 2016
Platform
  1. MacOS

ADVERTISEMENT

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?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
You will get a Div/0 error if nothing matches the criteria, so maybe col B has leading/trailing spaces.
 

Coachcasa

New Member
Joined
Feb 1, 2021
Messages
18
Office Version
  1. 2016
Platform
  1. MacOS

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
There is nothing wrong with the formula you originally posted, it works fine. The problem is in your data.
 

Coachcasa

New Member
Joined
Feb 1, 2021
Messages
18
Office Version
  1. 2016
Platform
  1. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
Does that make any sense to you?
Nope
+Fluff 1.xlsm
ABCDEF
1ScoreDifficultyWeatherLocation
250HardSunnyHome5
3#VALUE!EasyRainyWorka
470ModerateCloudySchool780
560Hard3Work6
6100hard3Work10
7 hard3Work
82001Work20
9
Main
Cell Formulas
RangeFormula
F4F4=AVERAGEIFS($A$2:$A$100,$B$2:$B$100,"Hard",$C$2:$C$100,3,$D$2:$D$100,"Work")
A2:A8A2=IF(E2="","",10*E2)
 

Forum statistics

Threads
1,144,164
Messages
5,722,856
Members
422,461
Latest member
kelleys315

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
Top