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

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    Accepted Answer

    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". 

  • Genevieve PGenevieve P admin
    Accepted Answer

    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 PGenevieve P admin
    Accepted Answer

    No problem! 🙂

Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭
    Accepted Answer

    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". 

  • AlbertoCLAlbertoCL
    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

  • Genevieve PGenevieve P admin
    Accepted Answer

    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

  • Thank you Genevieve, that was exactly the issue.

    Sorted! Thanks a million!

    Alberto

  • Genevieve PGenevieve P admin
    Accepted Answer

    No problem! 🙂

Sign In or Register to comment.