Find cells with value greater than value of other cell+1

[kcho]

Hi! I've got this thing that I really can't figure out, and it would be a big help if someone could give me advice.

I want to count the times that a value in one column has a value of one greater than the value of the corresponding cell in another column. (For example, A1=1 and B1=2, A2=1 and B2=2, etc.) I've tried =COUNTIFS(A1:A180, (B1:B180)+1) and =COUNTIFS(A1:A180, "="&(B1:B180)+1) with no luck. (It gives me a value of zero, but I can tell just by looking at the numbers that there should be at least a few times when this event occurs.)

Could someone suggest a way to do this, or let me know if it's even possible? Thank you very much!

[Dave H9]

I would use a helper column in C and compare A & B with an IF function to return 0 if false and 1 if true. You can then just total column C.

Hope that helps

Dave H

[Jarko28]

Hi,

Please try entering this array formula. Paste in the formula into the formula bar and press Control + Shift + Enter at the same time:

=SUM(IF(ABS(A1:A180-B1:B180)=1,1,0))

This will give you a count of occurrences where the difference between the two items is 1

If entered correctly, Excel will automatically place curly brackets around the formula after Control+Shift+Enter is pressed


{=SUM(IF(ABS(A1:A180-B1:B180)=1,1,0))}

[FDibbins]

Hi and welcome to the forum

An alternative would be this regular formula (no CTRL SHIFT enter needed)...

=SUMPRODUCT((A1:A10)*(B1:B10>A1:A10))

[Jarko28]

FDibbins

I don't think that this sumproduct formula counts the instances where the number between each row is 1... unless I am misunderstanding the request.

Thanks.

[FDibbins]

You are correct, I missed the "one" greater