Calculating Count of Projects by PM by Month

Kelcon
Kelcon
edited 12/09/19 in Formulas and Functions

Hi,

Trying to create formula to calculate How many projects start in a certain month by each Project Manager - My excel formulas are weak

Master sheet has columns:

Project Manager is Contact List (does this have to be a Text/Number Column?)

Project Start Date - is Date type Column

=COUNTIFS({Keller Project Summary - MASTER Range 11}, "Gerard" , ({Keller Project Summary - MASTER Range 12}, MONTH(@Cell)=1), IFERROR(@Cell) = 1,0)

 

What am i doing wrong is coming up with #unparseable

Comments

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Try: 

    =COUNTIFS({Keller Project Summary - MASTER Range 11}, "Gerard" , ({Keller Project Summary - MASTER Range 12}, IFERROR(MONTH(@cell), 0) =1)

    This is only counting Projects with in January. Change the "=1" to 2, 3, and so on to count additional months... 

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    An explanation of Nic's solution:

     

    You have the IFERROR misplaced. Generally I find it easier to work from the inside out when doing formulas like this. I will start with what I know I want

     

    MONTH(@cell)

     

    and then wrap it in he next portion

     

    IFERROR(MONTH(@cell), 0) = 1

     

    The way IFERROR works is

     

    =IFERROR(data to display, data to display if the first set is an error)

     

    So you would take the MONTH(@cell) and just drop that directly into the first portion of your IFERROR statement.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!