Querying my sheet

Alberto CONCEJAL LÓPEZ

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

Bassam Khalil

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:

Genevieve P.

Hi @AlbertoCL

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

Genevieve P.

No problem! 🙂

Bassam Khalil

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:

Alberto CONCEJAL LÓPEZ

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

Alberto CONCEJAL LÓPEZ

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

Genevieve P.

Hi @AlbertoCL

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

Alberto CONCEJAL LÓPEZ

Thank you Genevieve, that was exactly the issue.

Sorted! Thanks a million!

Alberto

Genevieve P.

No problem! 🙂