Averageifs Question

Hello,

I hope someone can help as i have been back and forth on this.

Basically, i want to extract an average based on the region and month from a data set, to work out average debtor days.

I have set up a spreadsheet whereby if i change the month in a certain cell, it automatically brings back the data i need for that month based on sumif formulas. However, in order to set up a debtor day target, i have to leave the sumifs function behind and set up an averageifs formula.

I have entered the month in the cell as 'Jan'
I have entered the following formula....which works
=AVERAGEIFS('Debtors Target'!CS:CS,'Debtors Target'!$B:$B,Data!$D:$D,'Debtors Target'!$CA:$CA,"Jan")
Immediately this brings back, the average value, based on the region in that spreadsheet, based on the month of Jan. I now need to add to this equation and include the data based on Feb, Mar, Apr etc.

I have tried doing the following:

=AVERAGEIFS('Debtors Target'!CS:CS,'Debtors Target'!$B:$B,Data!$D:$D,'Debtors Target'!$CA:$CA,"Jan")+AVERAGEIFS('Debtors Target'!CT:CT,'Debtors Target'!$B:$B,Data!$D:$D,'Debtors Target'!$CA:$CA,"Feb")+AVERAGEIFS('Debtors Target'!CU:CU,'Debtors Target'!$B:$B,Data!$D:$D,'Debtors Target'!$CA:$CA,"Mar") etc etc, keep adding the same formula.
The above doesnt work and comes back with div/0.

All of my sumifs calculations are based on the above logic and work well. I want the same logic to apply with the averageifs function, so that when i enter the month in the 'cell', it automatically brings back average debtor days for that region for that month. How can i add averageifs together?

I would very much appreciate if anyone can help with the above

Thanks

You will need to check the result of each individual AVERAGEIFS function and trap any error results. The Div/0 error means that there is at least one column where there are no results that meet the criteria. Untrapped, that error will be the result that you see, even if the remaining columns return a valid average.

=IFERROR(AVERAGEIFS('Debtors Target'!CS:CS,'Debtors Target'!$B:$B,Data!$D:$D,'Debtors Target'!$CA:$CA,"Jan"),0)+IFERROR(etc...

Note that adding averages may not give you the result that you expect, (SUMIFS()+SUMIFS())/(COUNTIFS()+COUNTIFS()) may be more accurate.

I would also suggest checking that your other formulas do actually return the correct results. The criteria, Data!$D:$D is the part that looks wrong, in that format it should only compare the contents of a single cell in that column, relative to the position of the formula, not the contents of every cell in the column.