SUMPRODUCT with Text

[cox_sam]

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

[#2]

first ensure your dates are numbers , as i see they are text dates, then it should work

[#3]

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

[#4 · Accepted solution]

@cox_sam 

[#5]

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!

[#6]

@cox_sam 

You are welcome.

Actually an error is in this part B2:B5-30 if text appears instead of number, IFERROR fixes that.