Two similar formulas, different results

Sumeluar

I am working with two ranges, one contains dates and the other one numbers which I wish to have them added by month such as any number that falls within being >= 01/01/18 but < 02/01/18 gets added. This is what I have thus far:

=SUMIF($[Payment Date]$26:$[Payment Date]$35, AND(@cell >= DATE([Due Date]87), @cell < DATE([Due Date]88), $Forecast$26:$Forecast$35)) This formula returns Zero (Which is incorrect)and this one:

=SUMIF($[Payment Date]$26:$[Payment Date]$35, AND(@cell >= DATE(2018, 1, 1), @cell < DATE(2018, 2, 1)), $Forecast$26:$Forecast$35) returns the correct number.

Can anyone help me understand why the first one is not working?

J. Craig Williams

I assume that [Due Date]87 and 88 are already dates. If so, you don't need (or want) the DATE() formula

=SUMIF($[Payment Date]$26:$[Payment Date]$35, AND(@cell >= [Due Date]87, @cell < [Due Date]88), $Forecast$26:$Forecast$35)

I am not sure what those two cells are, but I would likely put an absolute reference on them to be sure I was getting what I expected.

=SUMIF($[Payment Date]$26:$[Payment Date]$35, AND(@cell >= [Due Date]$87, @cell < [Due Date]$88), $Forecast$26:$Forecast$35)

Lastly, your first example has two closing parenthesis, when only one is needed. The AND is closed before the last argument.

I hope this helps.

Craig

 

 

Sumeluar

Thanks Craig, this is the formula that I'd need:

=SUMIF($[Payment Date]$26:$[Payment Date]$35, AND(@cell >= [Due Date]87, @cell < [Due Date]88), $Forecast$26:$Forecast$35)

Unfortunately the results are inaccurate, (@cell >= [Due Date]87 and @cell < [Due Date]88) and so on contain the the first of each month (01/01/18 through 01/01/19), All the other fields have dates and numbers accordingly but is not adding them though and as a result I get $0.00 for all the months.

Regards!

Sumeluar

 

J. Craig Williams

Sumelar,

You wrote:

Unfortunately the results are inaccurate, (@cell >= [Due Date]87 and @cell < [Due Date]88) and so on contain the the first of each month (01/01/18 through 01/01/19)

...

Is this supposed to 87 and 99 instead of 88? - you are implying there are 13 cells, not two. If that isn't it, can you share the sheet with me? 

Alternatively, if you are just looking for dates that match January 2018, then use MONTH(date) = 1 and YEAR(date) = 2018 in your formula instead.

craig.williams@ronin-global.com

Craig

Sumeluar

Thanks again Craig.

You are right there are 13 cells but only looking at two at a time such 87 & 88, 88 & 89, 89 & 90... and so on to 99, 87 being>= to the first of the month and 88 being less than the first of the following month, in reality I need to add for each month whatever is is the forecast.

In the other hand, you are absolutely right,  I am looking to add anything in the forecast to the respective month and have already tried MONTH(date) = 1 and YEAR(date) = 2018 which works great but when I drag the formula for the 12 months the month value needs to be changed one by one, is there a work around for that?

 

Regards!

Sumeluar

J. Craig Williams

=SUMIF($[Payment Date]$26:$[Payment Date]$35, AND(MONTH(@cell) = MONTH([Due Date]87), YEAR(@cell)= YEAR([Due Date]87)), $Forecast$26:$Forecast$35)

should then be for Jan 2018, correct?

Dragging down one row

=SUMIF($[Payment Date]$26:$[Payment Date]$35, AND(MONTH(@cell) = MONTH([Due Date]88), YEAR(@cell)= YEAR([Due Date]88)), $Forecast$26:$Forecast$35)

should be Feb 2018, and so on.

Craig

Sumeluar

Bingo!

Thank you so much for your help, this is exactly what I needed.

 

Regards!

Sumeluar

Sumeluar

I was wondering if that formula could be made to work with empty fields, currently I am getting: #INVALID DATA TYPE when either "Forecast" or "Payment date" is empty, which is bound to happen as the ranges are getting populated as project is  progressing.

 

Regards!

Sumeluar

J. Craig Williams

Easiest way is to wrap everything in IFERROR

=IFEFFOR(your current formula here, "Something happened*")

You can get fancier with checking for blank dates using ISDATE(), but likely IFERROR is a good start.

Craig

*That may be Microsoft's slogan now.

 

Sumeluar

Thanks Craig, I've tried using IFERROR at no avail (mind you that I am a first time Smartsheet user) I have searched several times for examples on how to use IFERROR and all I get as a result is $0.00, not sure why is not summing.

 

e.g. =SUMIF($[Payment Date]$24:$[Payment Date]$35, IFERROR(AND(MONTH(@cell) = MONTH([Due Date]91), YEAR(@cell) = YEAR([Due Date]91)), $Forecast$24:$Forecast$35))

 

Regards!

 

Sumeluar