Count IF with multiple criteria

[kcompton]

Hello - I am attempting to count all the values in column B that are equal to or greater than the corresponding value in Column A plus .6 -- for example, if B3 is greater than or equal to A3 plus .6, it would be counted; if B4 is not equal to or greater than A4 plus .6, it would not be counted. Is there a formula that would allow me to do this?
Thank you

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #ccffff; FONT-WEIGHT: bold">Grade</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff; FONT-WEIGHT: bold">GLE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">1</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">5.6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">5.7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">6.6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">2.7</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">6.5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2.1</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[bryonwoods30]

if your values are in a1 and b1, in c1 use the formula
=IF(B1>=A1+0.6,1,0)
then in any cell, just do a
=COUNTIF(C:C,1)

 

[T. Valko]

Hello - I am attempting to count all the values in column B that are equal to or greater than the corresponding value in Column A plus .6 -- for example, if B3 is greater than or equal to A3 plus .6, it would be counted; if B4 is not equal to or greater than A4 plus .6, it would not be counted. Is there a formula that would allow me to do this?
Thank you

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffff">Grade</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">GLE</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">1</TD><TD>#N/A</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">5.6</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">5.7</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">6.6</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">2.7</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">6.5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2.1</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Try this array formula**:

=SUM(IF(ISNUMBER(B2:B12),B2:B12>=A2:A12+0.6,1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[kcompton]

Thank you, but I need to do the calculation at the bottom of the column B.
So - In cell B13 I need to show the count of cells in B2:B12 that are greater than or equal to their corresponding cell in A2:A12.

Thanks for your help.

 

[kcompton]

Thank you!
=SUM(IF(ISNUMBER(B2:B12),B2:B12>=A2:A12+0.6,1))
worked!

 

[T. Valko]

Try this array formula**:

=SUM(IF(ISNUMBER(B2:B12),B2:B12>=A2:A12+0.6,1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Ooops!

I left out an important part of the formula.

Use this array entered** version:

=SUM(IF(ISNUMBER(B2:B12),IF(B2:B12>=A2:A12+0.6,1)))

 

[T. Valko]

Thank you!
=SUM(IF(ISNUMBER(B2:B12),B2:B12>=A2:A12+0.6,1))
worked!

I just noticed that I left out an important part of the formula.

Use this array entered** version:

=SUM(IF(ISNUMBER(B2:B12),IF(B2:B12>=A2:A12+0.6,1)))

 

[kcompton]

Thank you once again - I was just about to post that the formula returned too large a count of the cells that met the criteria when you re-posted the solution.
=SUM(IF(ISNUMBER(B2:B12),IF(B2:B12>=A2:A12+0.6,1))) Ctrl+Shift+Enter returns the correct count.
Many thanks again.

 

[T. Valko]

Thank you once again - I was just about to post that the formula returned too large a count of the cells that met the criteria when you re-posted the solution.
=SUM(IF(ISNUMBER(B2:B12),IF(B2:B12>=A2:A12+0.6,1))) Ctrl+Shift+Enter returns the correct count.
Many thanks again.

You're welcome. Thanks for the feedback!