Multiple DateIF formulas

[cfieser]

I have been searching to find the best way to sum multiple person's tenure to return an average, but I haven't been able to find a solution. Everyone is helpful and knowledgable on the boards so I came here. I am hoping someone can help me learn the best approach.

I used DATEIF to calculate the person's tenure and SUMPRODUCT to count how many fall into <1, 1-3, 3-5, 5-10 and >10 based on type.
I am trying to find the average tenure for each location based on type, when I tried to SUMPRODUCT I get a result of 0, same with IF.

I have attached a sample.

Thank you!

[daffodil11]

File came up "Incorrect file format or extension"

Can you upload again?

In the meantime, I'll generate random data.

[cfieser]

Sure

I really appreciate you taking the time to take a look. sample1.xlsx

https://www.dropbox.com/s/tppfnq7wpurx4h3/sample1.xlsx

File came up "Incorrect file format or extension"

Can you upload again?

In the meantime, I'll generate random data.

[daffodil11]

How about something like this?

Note, the formulas are entered as array. After the formula is typed in, hit Control+Shift+Enter instead of just normal Enter.

I'm not so great at SumProduct yet. Someone else may be able to truncate my expression into something a bit more efficient.

TENURECOUNT.xlsx

[cfieser]

That may work. I was trying to avoid adding another table to reference in the file, but it may be the best option. I am new with the date formulas other than =today() or now()

[daffodil11]

Ok, got it. Again, I'm not known for my simplicity or truncation but here are 5 formulas that work assuming dates are in column C with no extra tables necessary:

<=1

Please Login or Register  to view this content.

>1, <=3

Please Login or Register  to view this content.

>3, <=5

Please Login or Register  to view this content.

>5, <=10

Please Login or Register  to view this content.

>10

Please Login or Register  to view this content.

Remember, each needs entered with Ctrl+Shift+Enter

TENURECOUNT-complete.xlsx

[cfieser]

This works for the year criteria but not when adding in the criteria of location and type.
Thank you though!