Adding the amount of specific contacts across 3 different sheets and averaging based on network days

Matthew R

I am wondering if it is possible to combine a VLOOKUP formula with a COUNTIF in order add unique contacts in a single select contact column used in 3 different sheets and then find the average within the given network days. For example, I have 3 columns on 3 sheets that are named "Quality Inspector", "QC Inspector", and "Inspector", each of these columns are single select contact columns with common names between each one of them. I am looking to add these contacts between the 3 sheets and then find the average within the given number of network days. And ultimately display this average number onto a current metric sheet. Current formula shown below where I am able to average inspections based solely on 1 sheet, I am hoping it is possible to do this across 3 different sheets.

Thank you for any and all help.

Genevieve P.

Hi @Matthew R

The way I would do this is to create three separate COUNTIFS statements like you have above, and add them together with a + sign, then divide them by your working days.

For example:

=(COUNTIFS(formula sheet 1) + COUNTIFS(formula sheet 2) + COUNTIFS(formula sheet 3)) / $[Number of Workdays]$11

Or in your case:

=(COUNTIFS({Quality Inspector}, Inspector@row, {Date}, > TODAY(-100)) + COUNTIFS({QC Inspector}, Inspector@row, {Date sheet 2}, > TODAY(-100)) + COUNTIFS({Inspector}, Inspector@row, {Date Sheet 3}, > TODAY(-100)) / $[Number of Workdays]$11

Let me know if this makes sense and is what you're looking to achieve!

Cheers,

Genevieve

Genevieve P.

Hi @Matthew R

It looks like you just may not be separating the two values in your formula. The additions you're doing need to create one number, then you divide by another number.

In your second formula screen capture I can see that there is only two closing parentheses before you use the / sign - one for TODAY ) and one for the COUNTIFS ). This means that instead of dividing the combination of your multiple COUNTIFS statements, you're only dividing the last Countifs, then adding that value to the previous two.

Wrap another set ( around all of your COUNTIFS ) before dividing:

=(all of the COUNTIFS) / Number

Let me know if that works!

Genevieve

Genevieve P.

Hi @Matthew R

The way I would do this is to create three separate COUNTIFS statements like you have above, and add them together with a + sign, then divide them by your working days.

For example:

=(COUNTIFS(formula sheet 1) + COUNTIFS(formula sheet 2) + COUNTIFS(formula sheet 3)) / $[Number of Workdays]$11

Or in your case:

=(COUNTIFS({Quality Inspector}, Inspector@row, {Date}, > TODAY(-100)) + COUNTIFS({QC Inspector}, Inspector@row, {Date sheet 2}, > TODAY(-100)) + COUNTIFS({Inspector}, Inspector@row, {Date Sheet 3}, > TODAY(-100)) / $[Number of Workdays]$11

Let me know if this makes sense and is what you're looking to achieve!

Cheers,

Genevieve

Matthew R

This is exactly what I am after, I think I neglected the fact that you can add multiple COUNTIFS already, so thank you for stirring the gears and helping me realize that. One minor issue now, but I have found a work around for it so it is not too big of a deal. The formula you have provided works perfectly up until I need to divide the COUNTIF number by the amount of Network Days. When adding the divide by network days at the end it gives me an inaccurate average based on my COUNTED number.

The number "613" is displayed when using the formula without the divide by network days. When divided by 64 this should return a value of "9.57"/"9.58" more or less.

When adding the divide by network days it returns a value of "419" with an average of "6.55"

I have managed to use the formula you gave me without the divide by network days at the end to get my total value. And then I used a separate formula (=VALUE(Average1 / [Number of Workdays]11)) to display my true average.

Ultimately, it just did not give me an accurate average but I am sure a little fine tuning and it can all be written as one formula to find the average, hopefully this all made a little bit of sense.

Thank you so much for the help!

Genevieve P.

Hi @Matthew R

It looks like you just may not be separating the two values in your formula. The additions you're doing need to create one number, then you divide by another number.

In your second formula screen capture I can see that there is only two closing parentheses before you use the / sign - one for TODAY ) and one for the COUNTIFS ). This means that instead of dividing the combination of your multiple COUNTIFS statements, you're only dividing the last Countifs, then adding that value to the previous two.

Wrap another set ( around all of your COUNTIFS ) before dividing:

=(all of the COUNTIFS) / Number

Let me know if that works!

Genevieve

Matthew R

I see exactly what you are saying, I have adjusted the formula to the one above and it does exactly what I am after, Need to work on my parenthesis placement for future formulas!

Thank you for all the help on this one!

Genevieve P.

Wonderful, I'm glad it works, now! 🙂