Formulas

I have a report where I track estimate by project manager. I would like to count the estimate that need follow up that are past due for each project manager. I keep getting an unparseable error

=countif([Number of Est]@row,{J&B Estimating Log Range 5},=<TODAY())


"Number of Est" is a reference to the sheet I am working on and the "J&B Estimating Log Range 5" is from other sheet.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @gdooley@jbelectric

    If you have multiple criteria that you're looking for (a Project Manager and an Overdue Date), then you'll want to use COUNTFS, plural.

    COUNTIFS Function works like this:

    =COUNTIFS({Column 1}, "Criteria 1", {Column 2}, "Criteria 2")

     

    So in your case, something like this:

    =COUNTIFS({J&B Estimating Log Project Manager Column}, "Project Manager Name", {J&B Estimating Log Range 5}, @cell <= TODAY())


    If this hasn't helped, it would be useful to see a screen capture of the sheet you're referencing and your current sheet, but please block out sensitive data.

    Cheers!

    Genevieve

Answers

  • Nik Fuentes
    Nik Fuentes ✭✭✭✭✭

    for one, CountIf() only has two parameters, the range and the criterion to evaluate for each item in that range. If you delete the [Number of Est]@row part you should at least get past the #UNPARSABLE error, provided of course your range is the correct format/data type.

  • it works that way and yes the UNPARSABLE is clear but it counts all the estimate in the log that are over due. I just want it to count the estimates that need follow up/over due for each Project Manager. Should I be using an IF / AND command

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @gdooley@jbelectric

    If you have multiple criteria that you're looking for (a Project Manager and an Overdue Date), then you'll want to use COUNTFS, plural.

    COUNTIFS Function works like this:

    =COUNTIFS({Column 1}, "Criteria 1", {Column 2}, "Criteria 2")

     

    So in your case, something like this:

    =COUNTIFS({J&B Estimating Log Project Manager Column}, "Project Manager Name", {J&B Estimating Log Range 5}, @cell <= TODAY())


    If this hasn't helped, it would be useful to see a screen capture of the sheet you're referencing and your current sheet, but please block out sensitive data.

    Cheers!

    Genevieve

  • Genevieve P,

    Thank you that worked perfect.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!