COUNTIFS and SUMIFS functions?

I created a sheet that is a roll up of various information derived from a master sheet. I am using this to create a dashboard. However, I am stuck on a few formulas. I tried SUMIFS and COUNTIFS, but I cant get it right and either get invalid ref or unparseable.

  1. I would like to be able to count the number of stars appear for an individual (CA column).
  2. I would like to sum the dollar value for each person, that had a star (column not shown, but its contract values)


Tags:

Best Answer

  • AravindGP
    AravindGP ✭✭✭✭✭✭
    Answer ✓

    Hi @SadieW


    Here are the formulas that you can try

    1. Count of starts for an individual =COUNTIFS(Priority:Priroty, 1, CA:CA, "Full name as it appears in the cell")
    2. Sum of contract value for an individual =SUMIFS([Contract value column]:[Contract value column], Priority:Priroty, 1, CA:CA, "Full name as it appears in the cell")

    Thanks,

    Aravind GP| Principal Consultant

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭✭
    Answer ✓

    Hi @SadieW


    Here are the formulas that you can try

    1. Count of starts for an individual =COUNTIFS(Priority:Priroty, 1, CA:CA, "Full name as it appears in the cell")
    2. Sum of contract value for an individual =SUMIFS([Contract value column]:[Contract value column], Priority:Priroty, 1, CA:CA, "Full name as it appears in the cell")

    Thanks,

    Aravind GP| Principal Consultant

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

  • SadieW
    SadieW ✭✭✭
    edited 10/27/23

    Thank you soooo much. In the logic, when you place a 1, what does that actually mean, true?

    Also, if I use COUNTIFS and I want the formula to count the number of cells that are either blank or are greater than today's date, do I incorporate an OR function?

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 10/29/23

    @SadieW

    1 Yes, 1 means true.

    2 Yes, but you use COUNTIF.

    =COUNTIF(Date:Date, OR(ISBLANK(@cell), @cell > TODAY()))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!