Quirky Average Formula

Hello Gurus,

I need help once again... I have attached an example. I will try to make as simple as possible...

Average formula (I have been playing with AVERAGEIFS, but cannot solve) needs to see multiple columns of information to give a solution.

Things to know about full data:
There is more than one Area
There is more than one Service level
There are quite a few Codes (two below as an example and I can adjust code to add more)
Ranges are dynamic

The formula must average the duration time for unique Areas seen in column "A".

Then from column "D" for all deliveries that are "TRUE" in column "E" in combination with those that are "FALSE" in column "E" that in addition have a blank cell in column "C" or a qualifying reason in column "C". Let's just say that "phone" and "request" are qualifying reasons.

I hope that made sense because I need help and I feel a little nudge would do the trick!

Thanks in advance.

Try

=SUMPRODUCT((D2:D21)*((E2:E21="TRUE")+((E2:E21="FALSE")*(C2:C21="")+(C2:C21="Phone")+(C2:C21="request"))))/SUMPRODUCT(((E2:E21="TRUE")+((E2:E21="FALSE")*(C2:C21="")+(C2:C21="Phone")+(C2:C21="request"))))

It would help if gave a manual calculation for your posted file.

I apologize for not posting what was needed and thank you for your response however based on my calculation test using a straight AVERAGE formula the result should have been 5:41:13.

This is the formula I have been trying to make work:

=SUMIFS(D:D,A:A, A2,B:B, "Standard",C:C, "phone")+SUMIFS(D:D,A:A, A2,B:B, "Standard",C:C, "request")+SUMIFS(D:D,A:A, A2,B:B, "Standard",C:C, "")/COUNTIFS(A:A, A2,B:B, "Standard",C:C, "phone")+COUNTIFS(A:A, A2,B:B, "Standard",C:C, "request")+COUNTIFS(A:A, A2,B:B, "Standard",C:C, "")

BUT, it gives me the result of: 12:43:30

Then when I separate them in two separate cells like this:

=SUMIFS(D:D,A:A, A2,B:B, "Standard",C:C, "phone")+SUMIFS(D:D,A:A, A2,B:B, "Standard",C:C, "request")+SUMIFS(D:D,A:A, A2,B:B, "Standard",C:C, "")

=COUNTIFS(A:A, A2,B:B, "Standard",C:C, "phone")+COUNTIFS(A:A, A2,B:B, "Standard",C:C, "request")+COUNTIFS(A:A, A2,B:B, "Standard",C:C, "")

Then I divided the SUMIFS result by the COUNTIFS result using a simple =cell1/cell2 I get: 5:41:13

Is there a way to put these together like in the first formula I have?

Your formulae do not consider column E (TRUE/FALSE) as per your initial posting.

OMG I have been beating my head on the desk over some parenthesis??

And... RE the other post you made... I know... my approach to using the data has changed since my first post... sad thing is I put a lot of thought into my first post!!!

I'm going to test this out and get back to you... thank you so much for your input.

FYI

you can shorten the formulae .

=SUM(SUMIFS(D:D,A:A,A2,B:B,"Standard",C:C,{"phone","request",""}))

=SUM(COUNTIFS(A:A, A2,B:B, "Standard",C:C, {"phone","request",""}))

=SUM(SUMIFS(D:D,A:A,A2,B:B,"Standard",C:C,{"phone","request",""}))/SUM(COUNTIFS(A:A, A2,B:B, "Standard",C:C, {"phone","request",""}))

Thank you so much for your help. I couldn't have done it otherwise. I'm just about finished with my project now!

There is many other formulas that I have tweaked after this one and used. The final code that worked for my actual data was this:

=(SUM(SUMIFS(Details!$AE:$AE,Details!$C:$C, $A2, Details!$N:$N, "Standard", Details!$AF:$AF, "TRUE")+SUM(SUMIFS(Details!$AE:$AE,Details!$C:$C, $A2, Details!$N:$N,"Standard", Details!$U:$U,{"","CONFIDENTIAL","CONFIDENTIAL",CONFIDENTIAL","CONFIDENTIAL"}, Details!$AF:$AF, "FALSE"))))/(SUM(COUNTIFS(Details!$C:$C, $A2,Details!$N:$N, "Standard",Details!$AF:$AF, "TRUE")+SUM(COUNTIFS(Details!$C:$C,$A2,Details!$N:$N,"Standard",Details!$U:$U,{"","CONFIDENTIAL","CONFIDENTIAL","CONFIDENTIAL","CONFIDENTIAL"},Details!$AF:$AF,"FALSE"))))

Thanks Again!

You are very welcome.

Thank you for the feedback and rep.