Count IF with multiple criteria

kcompton

Board Regular
Joined
Mar 16, 2009
Messages
209
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

bryonwoods30

Board Regular
Joined
Feb 24, 2011
Messages
120
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Board Regular
Joined
Mar 16, 2009
Messages
209
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

Board Regular
Joined
Mar 16, 2009
Messages
209

ADVERTISEMENT

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

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

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

Board Regular
Joined
Mar 16, 2009
Messages
209
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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! :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top