SumIf with Date Range

gerry@prd
gerry@prd
edited 12/09/19 in Formulas and Functions

I created a project management financial sheet (client / projects / deliverables & invoicing).

I am trying to sum the value of invoices between a date range (screen grab).  This is what I thought would work:

=SUMIF(DTI:DTI, AND(@cell >= DATE(2019, 10, 1), @cell <= DATE(2019, 10, 31), InVAT:InVAT))

and it returns "0"?

The reason for the blank DTI cells is that they are at the parent row level.

I also tried:

=SUMIF(DTI:DTI, MONTH(@cell) = 10, InVAT:InVAT)

and it returns "#INVALID DATA TYPE".

Idealy I would like to report on invoice forcast by month and invoices due by month...

Appreciate your advice and help in advance!

Cheers

Gerry

Screen Shot 2019-11-19 at 21.47.14_0.png

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    Didn't close your and statement

    =SUMIF(DTI:DTI, AND(@cell >= DATE(2019, 10, 1), @cell <= DATE(2019, 10, 31)), InVAT:InVAT)

     

  • Thank you works perfectly now!

    So why is the following SUMIF not working?  Is it because of the date format in the cell?

    =SUMIF(DTI:DTI, MONTH(@cell) = 10, InVAT:InVAT)

    Thank you.

  • L_123
    L_123 ✭✭✭✭✭✭

    Sumif and countif are not compatible with the month formula for whatever reason. No amount of data type correction formulas (i.e. int, value, "." and others) that I have tried have been able to make it work. Maybe someone else can extrapolate a bit more on the reason behind this, if I had to guess I think it has something to do with the @cell reference having a data type issue with the month formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!