Help with a complex AVERAGEIFS formula

[willow1985]

I have the below SUMIF formula that works in my spreadsheet that I would like to modify to AVERAGEIFS based on all of the same criteria minus the column to Average.

I tried to sub out SUMIFS with AVERAGEIFS but all I get is a #DIV/0! error. Not sure why I am getting the error. I have also tried specified ranges AA2:AA1000 etc but still get the error.

=SUMIFS('WO Report'!$F:$F,'WO Report'!$M:$M,AG2,'WO Report'!B:B,List!$B$2)+(SUMIFS('WO Report'!$F:$F,'WO Report'!$M:$M,AG2,'WO Report'!Z:Z,List!$B$2))

=AVERAGEIFS('WO Report'!$AA:$AA,'WO Report'!$M:$M,AG2,'WO Report'!B:B,List!$B$2)+(AVERAGEIFS('WO Report'!$AA:$AA,'WO Report'!$M:$M,AG2,'WO Report'!Z:Z,List!$B$2))


Additional info:

COLUMN AA has blanks, zeros and whole numbers in which to look at and average.
Column M is a date and AG is the date match
Column B is a customer name and B2 is the customer name selected by a drop down menu
Column Z:Z just has the word "ALL" to the bottom of the data for when B2 wants to view ALL customers.

Any and all help is appreciated!

Thank you!

Carla

 

[Gerald Higgins]

I'm just testing your formula on the first half, so ignoring the Z:Z part.

It returns, for me, a #DIV/0 error IF there is not customer name in column B with a corresponding matching date in column M.
But, if the customer name in col B DOES match the target customer name in B2, and the corresponding date on the same line DOES match the target date, then I get a non-error result.

 

[willow1985]

There are matches so I am not sure why it is happening. I will edit the document and upload it to dropbox so you can take a look

 

[willow1985]

Here is the link:

https://www.dropbox.com/s/5viy7rdoac4gqa0/Data.xlsm?dl=0

The columns in blue under Graph Data you will see where I am trying to determine the averages but am receiving a #DIV/0 error

Thank you for all of your help!

Carla

 

[willow1985]

Better example: https://www.dropbox.com/s/l5jcauswmmrs70f/Data2.xlsm?dl=0

 

[willow1985]

Note: Asked this question on Excelforum.com in hopes for a quick response. First time cross posting and do not intend to in the future but am hoping to find an answer and my own research is not going well.

https://www.excelforum.com/excel-fo...-a-complex-averageif-formula.html#post5223111

Thank you to anyone who can help.

Carla

 

[willow1985]

Example of expected results:

On the Dashboard sheet, if Green is selected;

If you go to the GraphData sheet (where the Averageif formulas are), I should be getting a result of
Oct 20: 2
Oct 29: 4
In Column G

And

2.66 for the Month of October in Column M.

On the Dashboard sheet, if ALL is selected;

I would get a result of:

Oct 20: 2
Oct 29: 1
Oct 31: 3

And for the Month of October: 2.4

Hope this helps

Thank you very much

 

[Gerald Higgins]

Thanks, but sorry, I don't want to follow your link.
Others on this board may be happy to do that.

 

[willow1985]

Since I was unable to get help with this formula I found a work around and calculated it manually using a series of sumif and countif formulas and averaging the result.
A lot more formulas and calculating time than I would have liked but I needed a solution.