SOLVED

SUMPRODUCT with Text

%3CLINGO-SUB%20id%3D%22lingo-sub-3073393%22%20slang%3D%22en-US%22%3ESUMPRODUCT%20with%20Text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073393%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20write%20an%20equation%20that%20will%20count%20every%20instance%20two%20dates%20are%20more%20than%2030%20days%20apart.%20The%20spreadsheet%20is%20a%20living%20and%20growing%20document%20so%20having%20open%20ranges%20would%20be%20preferred.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20got%3CBR%20%2F%3E%3DSUMPRODUCT((--((A2%3AA5-30)%26gt%3BB2%3AB5))*(ISNUMBER(A2%3AB5)))%3C%2FP%3E%3CP%3Ebut%20this%20is%20getting%20%23VALUE%20when%20there%20is%20text.%20I%20will%20need%20to%20expand%20this%20formula%20to%20include%20probably%2020%20different%20columns%20but%20I%20need%20it%20to%20work%20with%20two%20or%20three%20columns%20first.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3EPreliminary%20Confirmation%20Date%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3EUpdate%20Report%20%231%3F%3C%2FTD%3E%3CTD%20width%3D%2220.85635359116022%25%22%3EUpdate%20Report%20%232%3F%3C%2FTD%3E%3CTD%20width%3D%2229.14364640883978%25%22%3EFinal%20Confirmation%20Dat%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3E3%2F18%2F2021%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3EN%2FA%3C%2FTD%3E%3CTD%20width%3D%2220.85635359116022%25%22%3EN%2FA%3C%2FTD%3E%3CTD%20width%3D%2229.14364640883978%25%22%3E3%2F22%2F2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3E3%2F18%2F2021%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E4%2F12%2F2021%3C%2FTD%3E%3CTD%20width%3D%2220.85635359116022%25%22%3ENo%3C%2FTD%3E%3CTD%20width%3D%2229.14364640883978%25%22%3E%26nbsp%3B5%2F10%2F2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3E10%2F26%2F2021%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E11%2F25%2F2021%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220.85635359116022%25%22%3E%26nbsp%3B1%2F4%2F2022%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2229.14364640883978%25%22%3E1%2F14%2F2022%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3E1%2F12%2F2022%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3ETo%20Be%20Updated%3C%2FTD%3E%3CTD%20width%3D%2220.85635359116022%25%22%3ETo%20Be%20Updated%3C%2FTD%3E%3CTD%20width%3D%2229.14364640883978%25%22%3ETo%20Be%20Updated%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20%3DIF(ISNUMBER)%20but%20still%20have%20the%20same%20problem%20and%20I'm%20not%20sure%20where%20to%20go%20from%20here%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3073393%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

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 DateUpdate Report #1?Update Report #2?Final Confirmation Dat
3/18/2021 N/AN/A3/22/2021
3/18/20214/12/2021No 5/10/2021
10/26/2021 11/25/2021  1/4/2022 1/14/2022
1/12/2022To Be UpdatedTo Be UpdatedTo 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

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

best response confirmed by cox_sam (Occasional Contributor)
Solution
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!

@cox_sam 

You are welcome.

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