Average using sumproduct in excel 2013

0
votes

I have table excel something like this :

enter image description here

Need to calculate average charge per year and using :

=SUMPRODUCT(--(WJ[Ownership]="Lease"),IFERROR(WJ[Total Charge]/DATEDIF(WJ[lease start],WJ[Lease end],"y"),""))/SUMPRODUCT(--(WJ[Ownership]="Lease"))

the formula is good when using ctrl + alt + enter, can any body help me to for alternative formula without Ctrl + shift + Enter and without iferror formula (the data contain some #DIV/0!)

1
excelexcel-formula

1 Answers

0
votes

What is your criteria for the Start and End Lease dates in your formula ?

Have you tried using AVERAGEIFS?

I feel it should solve your problem.