How to cerate formula of items closed each month

Options

I have been tasked to created a dashboard to capture all inquiries that have been closed by each responsible area on a monthly basis. 

Here is the formula that I used and received and error message:

=COUNTIFS({National Alliance Operation Closed Inquiri Range 2}, {National Alliance Operation Closed Inquiri Range 1}, "NA.ITS.ESCALATIONS", AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2020))

Best Answer

  • Beronica Muller
    Beronica Muller ✭✭✭✭
    Answer ✓
    Options

    @Werner Gerstacker, it worked!  Thanks. Now I have another question, is there a way that I can run a report or create a formula with those inquiries that were closed for the month and attach it as a source item in the dashboard widget? For example, management would like to see a listing of all inquiries that have been closed for the month of May without looking at the "Closed Inquiries" smartsheet (this sheet contains all closed inquiries from the beginning of time).

Answers

  • Werner Gerstacker
    Werner Gerstacker ✭✭✭✭✭
    Options

    Hi @Beronica Muller, I think you have your ranges and criteria mixed up.


    If your Range 1 holds the closing date and Range 2 holds the 'NA'Code I'd try:

    =COUNTIFS({National Alliance Operation Closed Inquiri Range 2}, "NA.ITS.ESCALATIONS, {National Alliance Operation Closed Inquiri Range 1}", AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2020))

  • Beronica Muller
    Beronica Muller ✭✭✭✭
    Answer ✓
    Options

    @Werner Gerstacker, it worked!  Thanks. Now I have another question, is there a way that I can run a report or create a formula with those inquiries that were closed for the month and attach it as a source item in the dashboard widget? For example, management would like to see a listing of all inquiries that have been closed for the month of May without looking at the "Closed Inquiries" smartsheet (this sheet contains all closed inquiries from the beginning of time).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!