WorksheetFunction.AverageIfs() Error

[Guslume]

I am having some issues to calculate an average number using WorksheetFunction.AverageIfs() with 5 different conditions (each one in a different range).
And when I run the code a Run time Error occurs (Unable to get the AverageIfs properties of the WorkSheet function).

There are two workbooks that I use, one with the database (data.xlsx) and another that I consolidate the data.

What I am trying to do is to calculate the average number of days that i have to perform each set of contracts that start on the same day for each starting date (some contracts start on the same date but have different deadlines).
Data regarding contract duration is available in the database.

What I observed is that the error occurs on the first date iteration probably because the second outcome is #DIV/0! (I tried using the excel formula and that's what happened - maybe the system doesn't deal very well with such error). But I am not sure about this conclusion

What should I do to avoid such problem? How can I circumvent this issue?

I attached both the samble database and the macro file

Thanks

Please Login or Register  to view this content.

[nankw83]

Hey Guslume,

You can remove the "worksheetfunction" & write your formula code like below to show the errors if the date is not found in your "data" sheet. By the way, I think your formula is missing the 4th condition which I have added for you

Please Login or Register  to view this content.

Alternatively, if you don't wish to show the error (which I prefer), you could add one variant type variable & check if the result is error, assign a zero or any other value as you wish to the cell like below

Please Login or Register  to view this content.

[Guslume]

Thank you for the help nankw83
Your suggestion was very helpful and you were right about the 4th condition being missed

I tried the code you wrote and it worked like a charm!

Thanks again!

[nankw83]

Glad to help & thanks for the reps

If the suggested solution takes care of your original question, please take a moment & mark this thread as 'SOLVED' from the Thread Tools at top menu bar above your first post