# Querying my sheet

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

Tags:

• ✭✭✭✭✭✭

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:

bassam.khalil2009@gmail.com

• Employee

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)

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Employee

No problem! 🙂

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭

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:

bassam.khalil2009@gmail.com

• edited 05/11/21

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

• Employee

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)

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Thank you Genevieve, that was exactly the issue.

Sorted! Thanks a million!

Alberto

• Employee