# Explore. Ask. Answer.

or Explore Discussions

# Querying my sheet

05/10/21
Accepted

Hello,

i have these two columns:

ESTIMATION PER HOUR (number)

RESOLUTION (numbers)

I want to query the sum of all ESTKM2/H matching if RES=5 and divide it by the number of items matching this query. In the end i want to calculate the mean of estimation among all resolution 5 jobs.

The result would be something like= the mean of resolution "5" jobs is taking "X" hours,

Thanks!!!!

Alberto

Popular Tags:

## Best Answers

• Hi @AlbertoCL

Hope you are fine, please try the following formula:

=SUMIFS([ESTIMATION PER HOUR]:[ESTIMATION PER HOUR], RESOLUTION:RESOLUTION, 5) / COUNTIFS(RESOLUTION:RESOLUTION, 5)

the following screenshot shows the result:

Best Regards

Bassam.M Khalil

☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful".

• Are you creating this formula in the same sheet, or as a cross-sheet reference? @Bassam Khalil's solution was for a formula within the same sheet.

If you're using {cross sheet references} then you only need to list the column once... like so:

=SUMIFS({estkm2/h}, {RES}, 5) / COUNTIFS({RES}, 5)

See this article for more information: Formulas: Reference Data from Other Sheets

Cheers,

Genevieve

• No problem! 🙂

## Answers

• Hi @AlbertoCL

Hope you are fine, please try the following formula:

=SUMIFS([ESTIMATION PER HOUR]:[ESTIMATION PER HOUR], RESOLUTION:RESOLUTION, 5) / COUNTIFS(RESOLUTION:RESOLUTION, 5)

the following screenshot shows the result:

Best Regards

Bassam.M Khalil

☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful".

• Thank you Bassam !

I'm afraid this is not working. What am i doing wrong?

=SUMIFS({estkm2/h}:{estkm2/h}, {RES}:{RES},5)/COUNTIFS({RES},{RES},5)

Thanks!

Alberto

• I have tried these:

=SUMIFS([estkm2/h]:[estkm2/h], RES:RES, 5) / COUNTIF({RES}, 5)

=SUMIFS({estkm2/h}:{estkm2/h}, RES:RES, 5) / COUNTIF({RES}, 5)

=SUMIFS({estkm2/h}:{estkm2/h}, RES, 5) / COUNTIF({RES}, 5)

=SUMIFS({estkm2/h}, RES, 5) / COUNTIF({RES}, 5)

And none of them worked (countif part yes it works but the numerator doesnt work, what am i doing wrong?)

Thanks!

Alberto

• Are you creating this formula in the same sheet, or as a cross-sheet reference? @Bassam Khalil's solution was for a formula within the same sheet.

If you're using {cross sheet references} then you only need to list the column once... like so:

=SUMIFS({estkm2/h}, {RES}, 5) / COUNTIFS({RES}, 5)

See this article for more information: Formulas: Reference Data from Other Sheets

Cheers,

Genevieve

• Thank you Genevieve, that was exactly the issue.

Sorted! Thanks a million!

Alberto

• No problem! 🙂

Sign In or Register to comment.