Complex if Formulas AVERAGEIFS, SUMIFS, COUNTIFS

Hello,

I am trying to create the above mentioned formulas to work with different columns and criteria but I cannot get it to work. I have looked at several websites but no luck finding something to work with.

Basically one of my formulas would be something like :
I want the average ratio of the departments :=” Human Resources” and “Administration”, only for Positions=”Staff I”,” Staff II”,” Services”, where the Standard Hours=”80”

I would like the values to be imputed as arrays although this would be the perfect solution.

This a sample of a dummy file I have created.

Sample.xlsx

Thanks

hoping this would help...

Hi,

Thanks a lot for helping but the results are not correct.

Thanks

Hi,

I figured it out. The key is to sum arrays for criteria with OR and regular multiplication for arrays with AND.

Formula:

Please Login or Register  to view this content.

Sample.xlsx

Thanks a lot for the help.

Yes because I need the arrays if they are one of the three options and they will never overlap because they are different categories withing the same fields.

Thanks