1

I am trying to create a spreadsheet that will figure out how much has been paid to date, from a list of payments in a separate row. I have tried many different options including using SumIF and CountIF. With both functions I am not receiving the desired result. I suspect that the date value in the expression is not being converted to a string. How can you change data types (date/value to string)? There are many functions that will turn a string into a date value but I don't know how to do the opposite and turn a date value into a string.

these are the formulas tried so far:

=SUMIF(2:2,CHAR(34)&"<="&B5&CHAR(34),Pmt)

where: CHAR(34)&"<="&B5&CHAR(34) = the expression = "<=5/1/16"

enter image description here

=COUNTIF(2:2,CHAR(34)&"<="&B5&CHAR(34))

enter image description here

I have tried moving the evaluation expression to a different cell and I have tried running the Expression (B5) through T() and Plaintext() to try to convert the date value to a string, with no success.

Please help, Thank you for your time!

1

I found the solution:

=SUMIF(2:2,CLEAN("<="&DATEVALUE(B5)),Pmt)

enter image description here

The solution was the proper use of the Clean() function.

I'll Leave this up if anyone has a similar issue.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .