# Averageifs formula used on more than 1 column to average

1. ## Averageifs formula used on more than 1 column to average

Hello, I am trying to calculate an average of 2 columns.
I want the yellow colums averaged on 3 criteria ranges - pink, orange and red if in those ranges there is written concrete text.
Here is a picture of what I'm trying to do. excel.jpg  Register To Reply

2. ## Re: Averageifs formula used on more than 1 column to average

You could use an array formula, entered with Ctrl-Shift-Enter, like

=AVERAGE(IF(A3:A36="13.10.2017r",IF(B3:B36="Characters",IF(C3:C36="BAPHA",D3:F36))))

(I don't have keys to enter your values for B3:B36 - this will work as long as the values in E3:E36 are string values.

Otherwise, your could add two AVERAGEIFS functions together, and divide by 2:

=(AVERAGEIFS(D3:D36,A3:A36,"13.10.2017r",B3:B36,"Characters",C3:C36,"BAPHA") + AVERAGEIFS(F3:F36,A3:A36,"13.10.2017r",B3:B36,"Characters",C3:C36,"BAPHA"))/2

Note that you could also use entire columns with the AVERAGEIFS so you don't have to worry about expanded ranges.

=(AVERAGEIFS(D:D,A:A,"13.10.2017r",B:B,"Characters",C:C,"BAPHA") + AVERAGEIFS(F:F,A:A,"13.10.2017r",B:B,"Characters",C:C,"BAPHA"))/2  Register To Reply

3. ## Re: Averageifs formula used on more than 1 column to average

Thanks for the fast reply, but still it doesn't work. The first formula gives me result of 0 which is impossible.(this is when the formula is entered just by pressing ENTER. If I use Ctr+Shif+Enter it gives me the same error Devision by 0 is impossible.(#DIV/0!)) And the second one is giving me an error which says it can't be devided by 0. Also what meand colum E to be "string value"? This could be the problem for the firs formula. The second.. I don't see mistake in it.  Register To Reply

4. ## Re: Averageifs formula used on more than 1 column to average

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.  Register To Reply

5. ## Re: Averageifs formula used on more than 1 column to average

Try using a version of the formula where you use cell references instead of strings, like

=AVERAGEIFS(D:D,A:A,A3,B:B,B3,C:C,C3)

That should return at least the value from D3 - if it does not, try formatting column D for number, and reenter the number in D3 as a number.  Register To Reply

6. ## Re: Averageifs formula used on more than 1 column to average

Yes, this formula returns the value from cell D3. So what's the point?  Register To Reply

7. ## Re: Averageifs formula used on more than 1 column to average

Are you required to use an AVERAGE() function? I would probably recall that the definition of average (arithmetic mean) is sum of values/count of values and perform the average as a combination of SUMIFS() and COUNTIFS() Maybe something like: =(SUMIFS(first column and criteria)+SUMIFS(second column and criteria))/(COUNTIFS(first column and criteria)+COUNTIFS(second column and criteria)) [I am assuming you can put the SUMIFS() and COUNTIFS() functions together].  Register To Reply

8. ## Re: Averageifs formula used on more than 1 column to average

The point is that the formula works when you have the correct values and/or combination of values. You either are not matching the actual values in your columns when you type them into the function or you do not have the specific combination of values that you are looking for: AVERAGEIFS returns an error when the combination of values is not found.  Register To Reply

9. ## Re: Averageifs formula used on more than 1 column to average

Good idea really, I'll try it now and tell if it works or not.  Register To Reply

10. ## Re: Averageifs formula used on more than 1 column to average

I've been stuck in this situation and my brain doesn't get it how the hell a computer program couldn't do such an easy thing... Probably it's my mistake and I don't write it corectly so that the computer understands me?! I don't know. It isn't necessary to use the AVERAGE/AVERAGEIF/AVERAGEIFS function, but that's just what I thought I could use in my case.
Well this happens now. It gives me the correct answer if the formula is
=AVERAGEIFS(D:D;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА")
but it's the average just on 1 of the colums I want. When I try to enter the other one it doesn't work as 1 full formula
=AVERAGEIFS(D:D;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА")+AVERAGEIFS(F:F;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА")
nor does this formula works (because I don't know if there sholud be more bracets like so =(AVERAGEIFS(D:D;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА"))+(AVERAGEIFS(F:F;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА"))
I think it's because of the + in the formula, I don't know if I am right or not.

P.S. I still don't get it how to hide the picture tho. Sorry.  Register To Reply

11. ## Re: Averageifs formula used on more than 1 column to average

If this works:

=AVERAGEIFS(D:D;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА")

This will work:

=AVERAGEIFS(F:F;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА")

If that does, try

=(AVERAGEIFS(D:D;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА") + AVERAGEIFS(F:F;A:A;"13.10.2017";B:B;"Младост";C:C;"ВАРНА"))/2

I just noticed that you have an extra ( in your function call AVERAGEIFS((..... That will cause the error.  Register To Reply

12. ## Re: Averageifs formula used on more than 1 column to average

Yes it worked! Thank you so much. But I had this formula entered before. The exact same.. why hadn't it worked earlier?  Register To Reply

13. ## Re: Averageifs formula used on more than 1 column to average

And if I want to average the temperatures for a certain period how would the formula look?
=IF("01.01.2017"<A:A<"30.03.2017";AVERAGE(D:D)+AVERAGE(F:F))  Register To Reply

14. ## Re: Averageifs formula used on more than 1 column to average

This will work

=(AVERAGEIFS(D:D;A:A;">=" & DATE(2017;1;1);A:A;"<="&DATE(2017;3;30)) + AVERAGEIFS(F:F;A:A;">=" & DATE(2017;1;1);A:A;"<="&DATE(2017;3;30)))/2

Though this may work - dates as strings need to match the system setting format....

=(AVERAGEIFS(D:D;A:A;">=1.1.2017";A:A;"<=30.3.2017") + AVERAGEIFS(F:F;A:A;">=1.1.2017";A:A;"<=30.3.2017"))/2

You could also replace the DATE functions with cell references.  Register To Reply

15. ## Re: Averageifs formula used on more than 1 column to average

Thank you so so much! I can't describe how thankful I am right now! I really appreciate your help!  Register To Reply

16. ## Re: Averageifs formula used on more than 1 column to average

You're welcome - can you mark the thread as solved so no one else will attempt to solve it?

Thanks,
Bernie  Register To Reply

17. ## Re: Averageifs formula used on more than 1 column to average

I hope I've done it right   Register To Reply

18. ## Re: Averageifs formula used on more than 1 column to average

Beautiful   Register To Reply