Count if column g +2 >= column h

[snerto]

I need a formula to count values that increased from one column to another by at least 2. So far I have a formula that will just count if it increases but I am unable to figure out how to make it be required to increase by so much.

The current formula I have is =SUMPRODUCT(--(H7:H80>=G7:G80))

Any help is appreciated.

 

[Mike LH]

Hi,

You were very close, try this.


=SUMPRODUCT(--(H7:H80-G7:G80>=2))

 

[snerto]

That gives me a value error. "A value used in the formula is of the wrong data type."

Hi,

You were very close, try this.


=SUMPRODUCT(--(H7:H80-G7:G80>=2))

 

[Mike LH]

Hi,

The formula will return a value error for one of 2 reasons---

1. You have text values in either of the ranges. Note that your text values make look like numbers but may actually be text.
2. less likely because your formula would also have returned a value error but if you have any #VALUE! errors in the range.

Try this. Put these 2 formula in unused columns and drag down.

=ISNUMBER(G7)
=ISNUMBER(H7)

These should all return TRUE and if any return FALSE then those cell(s) are text values

 

[snerto]

Thank you. I had a couple with a text value of "NULL". Now if I delete those and just leave the cells empty it counts them. Is this an appropriate formula to prevent that? It seems to work.

=SUMPRODUCT(--(H7:H80-G7:G80>=2),--(G7:G80<>""))

 

[Aladin Akyurek]

I need a formula to count values that increased from one column to another by at least 2. So far I have a formula that will just count if it increases but I am unable to figure out how to make it be required to increase by so much.

The current formula I have is =SUMPRODUCT(--(H7:H80>=G7:G80))

Any help is appreciated.

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(H7:H80*G7:G80),IF(H7:H80>=G7:G80+2,1)))

 

[Mike LH]

Thank you. I had a couple with a text value of "NULL". Now if I delete those and just leave the cells empty it counts them. Is this an appropriate formula to prevent that? It seems to work.

=SUMPRODUCT(--(H7:H80-G7:G80>=2),--(G7:G80<>""))

Hi,

No that won't work if there's text in the range. I would get rid of the text. Just an opinion. Unless there was an absolute need then I would never have any text in numerical ranges, it's messy, it messes up formula and makes for a poorly designed workbook. Sometimes you are where you are like say when the data are imported but if the numbers in the range are derived by manual entry or with formula then text can and should be avoided. You have another answer here that gets around the problem.