Need Help w/ Counting Week of

I am trying to count how many items on the list have a scheduled pickup date for this week. I already created a week number column. Here is the formula I am currently using but I keep getting an error message. What am I missing?


Tags:

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Use an IFERROR formula in your Week of Scheduled Return column:

    =IFERROR(WEEKNUMBER([Scheduled Pick-Up Date]@row), "")

    If there is a date in the left hand column, you will get the week number. If the date is blank, then the week number column for that row will be as well.

    Your COUNTIF function should be able to work properly though as it will just ignore the blanks and count the qualifying values.

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @Julie1247

    The #INVALID DATA TYPE errors in your Week of Scheduled Return are stopping the formula working correctly.

  • Julie1247
    Julie1247 ✭✭✭

    @Nick Korna , thanks for the feedback. Do you have a suggestion to help fix my issue? That Week of Scheduled Return column is a formula to calculate the week number of the date column to teh left of it. However if there is no date in that field yet then I get the #INVALID DATA TYPE. Is there a workaround to get the measure I am looking for: How many are scheduled to be picked up this week..?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Use an IFERROR formula in your Week of Scheduled Return column:

    =IFERROR(WEEKNUMBER([Scheduled Pick-Up Date]@row), "")

    If there is a date in the left hand column, you will get the week number. If the date is blank, then the week number column for that row will be as well.

    Your COUNTIF function should be able to work properly though as it will just ignore the blanks and count the qualifying values.

  • Julie1247
    Julie1247 ✭✭✭

    That worked!!! Both work now, thank you so much!

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    No problem, glad to have helped. ☺️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!