Count if column g +2 >= column h

snerto

New Member
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.

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Mike LH

Well-known Member
Hi,

You were very close, try this.

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

snerto

New Member
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

Well-known Member
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

New Member
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<>""))

MrExcel MVP
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

Well-known Member
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.

Replies
5
Views
667
Replies
1
Views
276
Replies
3
Views
2K
Replies
4
Views
360
Replies
4
Views
3K

1,195,905
Messages
6,012,218
Members
441,682
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back