Need to Combine 3 Formulas that have AverageIF in to one big one.

[Cow086]

Hi,
I have an averageif formula for 3 separate departments that my boss now wants me to have the 3 departments combined. So i want to take the 3 formulas and make it into one. Ive tried a few different ways and keep getting errors. Please help. The 3 formulas are as follows:
=IFERROR(AVERAGEIFS('Chrun Data'!$Z:$Z,'Chrun Data'!$U:$U,'Tenure Summary All'!D$115,'Chrun Data'!$X:$X,"No",'Chrun Data'!$Q:$Q,'Tenure Summary All'!$B14,'Chrun Data'!$P:$P,'Tenure Summary All'!$A14),"-")

=IFERROR(AVERAGEIFS('Chrun Data'!$Z:$Z,'Chrun Data'!$U:$U,'Tenure Summary All'!D$115,'Chrun Data'!$X:$X,"No",'Chrun Data'!$Q:$Q,'Tenure Summary All'!$B15,'Chrun Data'!$P:$P,'Tenure Summary All'!$A15),"-")

=IFERROR(AVERAGEIFS('Chrun Data'!$Z:$Z,'Chrun Data'!$U:$U,'Tenure Summary All'!D$115,'Chrun Data'!$X:$X,"No",'Chrun Data'!$Q:$Q,'Tenure Summary All'!$B17,'Chrun Data'!$P:$P,'Tenure Summary All'!$A17),"-")

The three departments are represented by the

'Chrun Data'!$Q:$Q,'Tenure Summary All'!$B15,
'Chrun Data'!$Q:$Q,'Tenure Summary All'!$B14,
'Chrun Data'!$Q:$Q,'Tenure Summary All'!$B17,

I am needing to do with with alot of formulas since the sheets that i have built im going to need to combine these departments alot.


Please advise

Excel Newbie
Cow086

 

[DanteAmor]

Hi and welcome to MrExcel board!

=SUMIFS('Chrun Data'!$Z:$Z,'Chrun Data'!$U:$U,'Tenure Summary All'!D$115,'Chrun Data'!$X:$X,"No",
'Chrun Data'!$Q:$Q,'Tenure Summary All'!$B15,'Chrun Data'!$P:$P,'Tenure Summary All'!$A15)/
SUMPRODUCT(('Chrun Data'!$U:$U='Tenure Summary All'!D$115)*('Chrun Data'!$X:$X="No")*
(('Chrun Data'!$Q:$Q='Tenure Summary All'!$B15)+('Chrun Data'!$Q:$Q='Tenure Summary All'!$B14)+('Chrun Data'!$Q:$Q='Tenure Summary All'!$B17))*
('Chrun Data'!$P:$P='Tenure Summary All'!$A15))

It would be easier to understand if you used named ranges for each column, for example.
As sumproduct is an array formula, it is recommended that you do not use the entire column P:P, use only a range, for example P2:P3000

=SUMIFS(col_z, col_u,'Tenure Summary All'!D$115,col_x,"No",
col_q,'Tenure Summary All'!$B15, col_p,'Tenure Summary All'!$A15) /
SUMPRODUCT((col_u='Tenure Summary All'!D$115)*(col_x="No")*
((col_q='Tenure Summary All'!$B15)+(col_q='Tenure Summary All'!$B14)+(col_q='Tenure Summary All'!$B17))*
(col_p='Tenure Summary All'!$A15))

 

[Cow086]

Thank you, Ive heard of using named ranges to make things easier to read and have never used them before ill have to try it

 

[DanteAmor]

Sorry, I missed adding the amounts in the first part of the formula, so it should be like this:

=SUMPRODUCT(('Chrun Data'!$Z:$Z)*
('Chrun Data'!$U:$U='Tenure Summary All'!D$115)*
('Chrun Data'!$X:$X="No")*
(('Chrun Data'!$Q:$Q='Tenure Summary All'!$B15) + ('Chrun Data'!$Q:$Q='Tenure Summary All'!$B14) + ('Chrun Data'!$Q:$Q='Tenure Summary All'!$B17)) *
('Chrun Data'!$P:$P='Tenure Summary All'!$A15)) /
SUMPRODUCT(('Chrun Data'!$U:$U='Tenure Summary All'!D$115)*('Chrun Data'!$X:$X="No")*
(('Chrun Data'!$Q:$Q='Tenure Summary All'!$B15)+('Chrun Data'!$Q:$Q='Tenure Summary All'!$B14)+('Chrun Data'!$Q:$Q='Tenure Summary All'!$B17))*
('Chrun Data'!$P:$P='Tenure Summary All'!$A15))

With Named range:

=SUMPRODUCT((col_z)*
(col_u='Tenure Summary All'!D$115)*
(col_x="No")*
((col_q='Tenure Summary All'!$B15) + (col_q='Tenure Summary All'!$B14) + (col_q='Tenure Summary All'!$B17)) *
(col_p='Tenure Summary All'!$A15)) /
SUMPRODUCT((col_u='Tenure Summary All'!D$115)*(col_x="No")*
((col_q='Tenure Summary All'!$B15)+(col_q='Tenure Summary All'!$B14)+(col_q='Tenure Summary All'!$B17))*
(col_p='Tenure Summary All'!$A15))