Forum Discussion
SUMPRODUCT with Text
Hi all,
I am trying to write an equation that will count every instance two dates are more than 30 days apart. The spreadsheet is a living and growing document so having open ranges would be preferred.
I have got
=SUMPRODUCT((--((A2:A5-30)>B2:B5))*(ISNUMBER(A2:B5)))
but this is getting #VALUE when there is text. I will need to expand this formula to include probably 20 different columns but I need it to work with two or three columns first.
| Preliminary Confirmation Date | Update Report #1? | Update Report #2? | Final Confirmation Dat |
| 3/18/2021 | N/A | N/A | 3/22/2021 |
| 3/18/2021 | 4/12/2021 | No | 5/10/2021 |
| 10/26/2021 | 11/25/2021 | 1/4/2022 | 1/14/2022 |
| 1/12/2022 | To Be Updated | To Be Updated | To Be Updated |
I have tried =IF(ISNUMBER) but still have the same problem and I'm not sure where to go from here
5 Replies
- I had to make a slight tweak since I had written my formula wrong. It should have been, SUMPRODUCT if B2:B5-30>A2:A5. Basically if the dates in column B are more than 30 days past the dates in column A, count it. I used your formula but just changed those ranges and it's perfect. Thank you!
You are welcome.
Actually an error is in this part B2:B5-30 if text appears instead of number, IFERROR fixes that.
first ensure your dates are numbers , as i see they are text dates, then it should work
On my actual spreadsheet, they are Custom m/d/yyyy;;"" When I change it to dates or to numbers, I still have the same #VALUE! problem unfortunately
