Calculating info in a column based on a "greater than" date & info in another column

I apologize if this was covered elsewhere- I tried a search first and found similar threads, but nothing that helped. I have a report that lists deficiencies- each deficiency has it's own ID number, which I put in column A (specifically, A3:A192). In column I (I3:I192) is the inspection date- the date the deficiency was first noted. In J (J3:J192) is the completion date- when the deficiency was corrected. We have 30 days to correct deficiencies and close them out. So, I need a formula that tells me how many open deficiencies that I have that are over 30 days. There are a total of 15 open deficiencies. This should be simple, but I'm stymied. I tried a couple of formulas to show how many were over 30 days.

First, I tried keeping it simple with =COUNTIF(I3:I192,">" & TODAY()-30) but that rendered 0. I also tried a variation of replacing "TODAY()" with cell B195 where I also had a TODAY() formula. Same result: zero.

Next, I tried getting fancier with =SUMPRODUCT(--ISNUMBER(A3:A192),--(I3:I192>B195-30),--(J3:J192="")). That gave me 15. Problem is, 15 is the total number of open deficiencies that I have, under and over 30 days.

Any suggestions?

Hi Glen,

That almost did it. The result was reduced to 10. Problem is, only 9 are actually greater than 30 days old. The other 6 are under. But the formula you came up with is way better than mine- I'm going mess around with it, see if I can get it working. As soon as I can figure out how, I'll upload my workbook. The attach function doesn't seem to be working for me.

You're definitely right about that: I am making a mistake- I just can't figure out where! I copied a small amount of the data from my original workbook and put it into a another excel doc to try the formula on a smaller amount. The data contained 14 of the original 15 open deficiencies. 8 are over 30 days, 6 are under. This time, the formula gave me the correct amount of open orders over 30 days: 8. So, now I'm really confused. I've gone over and over my original and can't find the error I am making. Maybe it's a formatting error. Anyway, I hope you don't mind, but I have attached both my original doc, "EOC WOs" and the smaller one that is displaying the correct number ("Sample").

Ok, scratch that- I went looking one more time and realized that the range on the formula was one row too many. I changed it and now it works perfectly.

Thank you very much!

Thank you Glenn and AVK- Now I just have to figure out how to put the [Solved] in the thread headline.

Done- thank you Sir!