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"
=COUNTIF(2:2,CHAR(34)&"<="&B5&CHAR(34))
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!
Best Answer
I found the solution:
=SUMIF(2:2,CLEAN("<="&DATEVALUE(B5)),Pmt)
The solution was the proper use of the Clean() function.
I'll Leave this up if anyone has a similar issue.