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.

 

[Coachcasa]

That would suggest that
a) Nothing meets the criteria.
b) The values in the average range are text
c) all the rows that meet the criteria have 0 in the average range.

I figured out that error because my first criteria was pulling from a row that had a blank value. When I changed that, I'm back to the #VALUE! error. This is driving me crazy! I do appreciate your help though!

 

[Coachcasa]

That would suggest that
a) Nothing meets the criteria.
b) The values in the average range are text
c) all the rows that meet the criteria have 0 in the average range.

So I cleaned up my calculations. There was an empty row at the top of my list of entries. Whenever I enter new data I insert a row and put the new data at the top. I got rid of that and my DIV error went away and I'm back to the VALUE error. I tried putting #N/A in all of the empty cells in my list where I don't have data, thinking maybe that was causing an issue, but that didn't change anything, except screw up other parts of my sheet, so I got rid of those. I really appreciate all of your suggestions even if I haven't made any progress figuring this out.

 

[Fluff]

Can you upload your file to a share site such as OneDrive or Google drive? If so mark for sharing & post the link you get to the thread.

 

[Coachcasa]

Can you upload your file to a share site such as OneDrive or Google drive? If so mark for sharing & post the link you get to the thread.

I would love to so that you could look at it directly, but unfortunately, there is some data that I can't share that is in there. I'm looking to see if I can take off labels and other parts and copy part of this to a blank sheet that I then can share.

 

[Fluff]

Ok, that understandable.

 

[Coachcasa]

Ok, that understandable.

Let's tackle this from a different angle. In my "Column A" every cell is filled with a value. However, in my ranges for my criteria in "columns B,C,D, there are blank cells because I had no data for that "result". Is there a way to force my AVERAGEIFS formula to ignore blank cells within the range? I can't set that in stone and only pick filled cells, because I keep inserting lines at the top with new data, so the cell numbers lower down change. What about using <> in my formula? Is there a way to add that in? Thanks again.

 

[Fluff]

The blank cells in B, C & D shouldn't be a problem, it's just col A that matters.

 

[Coachcasa]

The blank cells in B, C & D shouldn't be a problem, it's just col A that matters.

 

[Coachcasa]

So, in all four columns, I have it set to pull from rows 1-1000, even though I am only using 300 rows right now and my data starts at row 4. I did this because I am constantly inserting new rows at the top of my data in row 4 (row 3 are the headers and rows 1 and 2 are the titles) and that is pushing the old data down. I did this so I don't have to constantly edit my formula to take in all of the new rows inserted (when I insert it pushes my start row down 1) and the older data at the bottom that otherwise would go beyond my data range when pushed down. However, these extra rows without data in them are creating the VALUE error. When I change just Column A's range to 4-300, I still get the VALUE error. When I change all 4 columns range from 4-300, the VALUE error changes to the DIV/0 error. I'm pretty sure the DIV error is because in columns B, C, and D I have some cells that are blank, especially in the older data because I added these columns later on and I don't have the data to fill in these cells and can't go back and get it, so I leave them blank. I tried switching these cells to #N/A, but then that threw off other formulas in my sheet that didn't like that, so I took it back out. I wish I could share the sheet with you, but because of the data. Three questions for you:

1. Is there a way when I insert new rows at the top to not have my formulas in cells below this have their rows in the formula shift down? I find this in a lot of my formulas when I insert rows above them and they are calculating from the rows below the new row. I want the formula to stay exactly the same even though the cell with the formula has shifted down and the rows it's pulling from have been shifted down

2. Is there a way to calculate AVERAGEIFS and have blank rows in the formula so I don't constantly have to change my formula every time I inset a new row?

3. Is there a way for AVERAGEIFS to ignore blank cells altogether in the ranges I'm calculating or pulling data from?

4. Here's an additional new question for you: I have all of my data-columns A-D on the left side of my sheet. After Column D, in cell E4, I used the freeze and splitting panes so that I can split the left side of my sheet from a right side in columns E-K. In column E-K is where all of my calculating of my data is going on to show me various averages, minimums, etc. Is there a way to split my sheet so that when I scroll on the left or right the other side stays where it is? Meaning they scroll independently? I know you can do this with rows, but I haven't found a way to do it with columns. My goal is to have the data on one side easily visible and the calculation on the other side easily visible, but separate.

Again thank you for all of your responses and sorry if I'm being a pain in the rear! I'm just trying to figure this out and can't quite seem to do it on my own. Unless you are someone else can help me through this, my options seem to be to forget this problematic calculation and go without this calculation or look for a different way to approach this and not use the AVERAGEIFS formula. I don't know what the alternative would be.

 

[Fluff]

Blank cells & formulae that return "" should not be a problem.

+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	l
4	70	Moderate	Cloudy	School	7	80
5	60	Hard	Sunny	Work	6
6	100	hard	Sunny	Work	10
7		hard	Sunny	Work
8	200		sunny	Work	20
9
Main

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

The only way I know of AverageIfs to return #VALUE! is if you have that error in a col A cell that meets the criteria

+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	#VALUE!
5	60	Hard	Sunny	Work	6
6	100	hard	Sunny	Work	10
7	#VALUE!	hard	Sunny	Work	a
8	200		sunny	Work	20
9
10
Main

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