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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)))
 
Upvote 0
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)))
 
Upvote 0
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.
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,217,677
Messages
6,137,941
Members
450,100
Latest member
mscetr

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
Back
Top