=AVERAGEIFS Work around #DIV0! error

[lnapier]

How can I use the AVERAGEIFS function to average a range that includes all the row columns in a worksheet without returning the #DIV0! error value? I'm using the below forumla to return the average of rows meeting multiple criteria. I want to have the forumla return "16", but I'm getting the #DIV0! error value. I need to have the formula scan the entire column, becaus the row count will grow as I add rows of data each day. I want the formula to automatically figure the average when I cut and paste the data into each column. I'm reading that this formula will return the #DIV0! error value if it includes <Blank> cells. I believe this is what is happening. Is there a way around this? How can I have the formula automatically constrain to the first and last data entry in A:A to figure the average?

=AVERAGEIFS(A:A,B:B,"="&"APPLE",C:C,"="&"RED")

A B C
------ ------- -------
12 APPLE RED
10 ORANGE ORANGE
15 APPLE RED
26 APPLE RED
<BlanK> <BlanK> <BlanK>
<BlanK> <BlanK> <BlanK>
<BlanK> <BlanK> <BlanK>
<BlanK> <BlanK> <BlanK>
| | |
| | |
| | |
V V V
All the way to the last cell int the spreadsheet

 

[barry houdini]

No that shouldn't return #DIV/0! in this case - such a result means you either have no rows that match the criteria....or column A values are not numeric - what result do you get with COUNTIFS like this?

=COUNTIFS(B:B,"APPLE",C:C,"RED")

 

[dispelthemyth]

Your formula is fine and it should return 17.33 not 16 i believe
(12 + 15 + 26) / 3 = 17.33

 

[lnapier]

Barry and Dispel- Thanks for the quick input. Barry was correct. Column A:A was formatted as "General". I converted to numeric and I'm no longer getting the error. I have another problem though. In the below formula I need to incorporate some sort of OR function to average rows that reflect APPLE "AND" RED that are ROTTEN OR FRESH.

The way I have it below adds two averages together, which doesn't come up with the correct result. Any ideas?
=
AVERAGEIFS(A:A,B:B,"="&"APPLE",C:C,"="&"RED",D:D,"="&"ROTTEN")+
AVERAGEIFS(A:A,B:B,"="&"APPLE",C:C,"="&"RED",D:D,"="&"FRESH")

 

[barry houdini]

I don't believe you can do that with AVERAGEIFS, either use an array formula like this

=AVERAGE(IF(B:B="APPLE",IF(C:C="RED",IF((D:D="ROTTEN")+(D:D="FRESH"),A:A))))

confirmed with CTRL+SHIFT+ENTER

...or a regular formula like this

=SUM(SUMIFS(A:A,B:B,"APPLE",C:C,"RED",D:D,{"ROTTEN","FRESH"}))/SUM(COUNTIFS(B:B,"APPLE",C:C,"RED",D:D,{"ROTTEN","FRESH"}))