Countifs Formula

Options
AAL
AAL ✭✭✭✭

Hi Smartsheet Team,

I have captured this student id no. 2307647 Programming session hours is 1.5 hours. (see below)


Then, I worked on the metrics sheet to show the hours on the programming session - Complete. It shows only 1 hour, even though I added the decimal spaces. the formula for Programming sessions - Complete is

=COUNTIFS({Merit Scholars- Reporting Student ID no}, [Student ID]@row, {Merit Scholars- Reporting Program or Events}, "Programming Session")

Can you please review my formula and adjust it to display 1.5 hours instead of just 1 hour? Thank you!


Tags:

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/06/23 Answer ✓
    Options

    The COUNTIFS is counting the number of rows where the criteria are true.

    If looks like you want to count hours if

    {Merit Scholars- Reporting Student ID no} equals [Student ID]@row

    and

    {Merit Scholars- Reporting Program or Events} equals "Programming Session"

    I think you need a SUMIFS instead. This will let you sum the total of a given column (hours) if the criteria are met.

    Change the parts in bold from

    =COUNTIFS({Merit Scholars- Reporting Student ID no}, [Student ID]@row, {Merit Scholars- Reporting Program or Events}, "Programming Session")

    to

    =SUMIFS({reference for the hours column},{Merit Scholars- Reporting Student ID no}, [Student ID]@row, {Merit Scholars- Reporting Program or Events}, "Programming Session")

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/06/23 Answer ✓
    Options

    The COUNTIFS is counting the number of rows where the criteria are true.

    If looks like you want to count hours if

    {Merit Scholars- Reporting Student ID no} equals [Student ID]@row

    and

    {Merit Scholars- Reporting Program or Events} equals "Programming Session"

    I think you need a SUMIFS instead. This will let you sum the total of a given column (hours) if the criteria are met.

    Change the parts in bold from

    =COUNTIFS({Merit Scholars- Reporting Student ID no}, [Student ID]@row, {Merit Scholars- Reporting Program or Events}, "Programming Session")

    to

    =SUMIFS({reference for the hours column},{Merit Scholars- Reporting Student ID no}, [Student ID]@row, {Merit Scholars- Reporting Program or Events}, "Programming Session")

  • AAL
    AAL ✭✭✭✭
    Options

    Thank you, KPH.. Now I got it.

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    Great news! Glad to have helped.