AVERAGEIF Multiple Columns

Hi,


I am trying to calculate an average of 5 months in 5 columns (D:H) if criteria in column A is met


I have tried the following but doesn’t seem to be working


=averageif(A:A,”Hire Purchase”,D:H)


Any suggestions would be appreciated.


Thanks in advance.

Here is the sample.

Thanks for trying, anyway.

Originally Posted by Ganesh7299

Hi, As already you have average in column "J", you can simply use vlookup for this

Formula:

Please Login or Register  to view this content.

I was trying to avoid creating another column for the average and calculate it in the cell. Thanks for the advice anyway.

You're a star. I knew I could count on you here. As always, very helpful.


Many thanks again.

Originally Posted by akhileshgs

Hi... got it..


Attachment 245527

I am reviving an old thread because it is very similar to the question I have. Also, I don't think the question was answered all the way. The last poster, Ganesh7299, is correct. The solution provided in the reposted sample file returns the average only for the values in the row where the first occurrence of Hire Purchase happens. In the example, that is D4 through H4.

What Ganesh7299 points out is that there is another row where Hire Purchase appears in Row 8. How do you get those values in cells D8 to H8 to also be part of the average? Ganesh7299 provides a solution, but when I paste it into the sample, I get a #NAME error. I thought there might be a few extra spaces or something in the formula, but that did not fix it. This is the revised version.

=SUMPRODUCT((D4:H11)*(A4:A11=”Hire Purchase”))/(COUNTIF(A4:A11,”Hire Purchase”)*5)

This is exactly what I want to know (use only some rows of data based on a criteria). I'm using Excel 2013 and Windows 7. Thanks.

Drat! Sorry I missed that. I will post a new one. Thanks.