SumIf with Date Range

gerry@prd

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

L_123

Didn't close your and statement

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

 

gerry@prd

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

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.