# Using two conditions in COUNTIF

#### hence

##### New Member
Hi, I have the following data:
A B C D
Phone # Worked PI NI
55555555 Y 0.00 2.00
66666666 10.00 12.00
77733222 Y 3.50 8.00
45466666 Y 0.00 0.00

table is 4 columns (phone # / Worked / PI / NI) wide by numerous rows deep...

Basically, I want to do a COUNTIF function that will basically count the number of entries that have a PI greater than 0 AND a Y in the worked column.. if that makes any sense I don't want it counting ones where the PI and NI columns are zero...

I can't seem to figure it out: I thought that something like this might work:

COUNT(IF(B2:B5, "Y") AND (C2:C5, ">.01"))

Any help would be greatly appreciated!!!

Drew

### 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

#### Erdinç E. Karaçam

##### Board Regular
Hi there,

Table sample :

<table border=1><tr><td align=left valign=center><font size=1 color=red face=verdana>Gönderen XL Ver.:</font><font size=1 face=verdana> Office 2003 / </font><font size=1 color=red face=verdana>OS Ver.:</font><font size=1 face=verdana> Windows XP </font></td></tr><tr valign=top><td><table border=0 bgcolor=d4d0c8 cellspacing=1 cellpadding=1 align=center><tr align=center valign=center bgcolor=white><td bgcolor=d4d0c8 align=center><font color=black size=2>+</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>A</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>B</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>C</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>D</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>1</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Phone#</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Worked</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>PI</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>NI</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>2</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>55555555</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Y</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>1</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>2</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>3</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>66666666</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>X</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='-2'>-2 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>12</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>4</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>77733222</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Y</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='-1'>-1 (ƒx)</ACRONYM></font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>8</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>5</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>45466666</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>Y</font></td><td bgcolor=white nowrap=true align=right><font size=1 color=black face=verdana>2</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>0</font></td></tr></table></td></tr><tr><td bgcolor=f0f8ff align=center><font color=black size=1 face=verdana>XLtoHTML v1.1 / ExcelTürkiye - 2006©</font></td></tr></table>

Method 1:
Code:
``=SUMPRODUCT((\$B\$2:\$B\$5="Y")*(\$C\$2:\$C\$5>0.1))``

Method 2:
Write the formula and press CTRL + SHIFT + ENTER (Not only Enter; because it's a array formula):
Code:
``=SUM(IF(\$B\$2:\$B\$5="Y";IF(\$C\$2:\$C\$5>0,1;1;0);0))``

#### venkat1926

##### Well-known Member
try this

=SUMPRODUCT((\$B\$2:\$B\$5="Y")*(\$C\$2:\$C\$5>0))

venkat

#### hence

##### New Member
thanks so much! the last one worked!! wasn't even aware of that formula! still kinda new..

I have one more question... I have a deadline for a project for Dec 01 2006.

I want to create a formula that automatically calculates the remaining BUSINESS days till that date.. ie.. no weekends.. so 5 days a week

is this possible? can it update as each day passes automatically?

tks again!

drew

#### venkat1926

##### Well-known Member

see excel help "networkdays worksheet function"
in the formula the start date can be today() so that the remaining days can be updated each day.

problem-- networking days take into account the declared holidays also. . read the help completely you will understand.

if you need further help revert to newsgroup

venkat

#### Erdinç E. Karaçam

##### Board Regular
Hi,

i prepared a User Definied Function for Count Weekdays between 2 dates.

Using of U.D.F.:
Code:
``=WeekendCount("14/10/2006";"31/12/2006")``

Or:

<table border=1><tr><td align=left valign=center><font size=1 color=red face=verdana>Gönderen XL Ver.:</font><font size=1 face=verdana> Office 2003 / </font><font size=1 color=red face=verdana>OS Ver.:</font><font size=1 face=verdana> Windows XP </font></td></tr><tr valign=top><td><table border=0 bgcolor=d4d0c8 cellspacing=1 cellpadding=1 align=center><tr align=center valign=center bgcolor=white><td bgcolor=d4d0c8 align=center><font color=black size=2>+</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>A</font></td><td bgcolor=d4d0c8 width=60><font color=black size=1 face=verdana>B</font></td></tr><tr align=left bgcolor=white height=20><td align=center valign=center bgcolor=d4d0c8 width=10><font color=black size=1 face=verdana>1</font></td><td bgcolor=white nowrap=true><font size=1 color=black face=verdana>12/31/2006</font></td><td bgcolor=white nowrap=true align=center><font size=1 color=black face=verdana><ACRONYM title='=WeekendCount(TODAY();A1)'>23 (ƒx)</ACRONYM></font></td></tr></table></td></tr><tr><td bgcolor=f0f8ff align=center><font color=black size=1 face=verdana>XLtoHTML v1.1 / ExcelTürkiye - 2006©</font></td></tr></table>

Code:
``=WeekendCount(TODAY();A1)``

Code:
``````Function WeekendCount(First_Day As Date, Last_Day As Date) As Date

Dim i As Integer
Dim Quantity As Integer
Dim Counter As Integer

Quantity = Last_Day - First_Day

For i = 1 To Quantity
If Weekday(First_Day) = 7 Or Weekday(First_Day) = 1 Then
Counter = Counter + 1
End If
First_Day = First_Day + 1
Next i

WeekendCount = i - Counter - 1

End Function``````

U.D.F. returned for 14/10/2006 - 31/12/2006 Dates: 55

#### jindon

##### MrExcel MVP

Function WeekendCount(First_Day As Date, Last_Day As Date) As Date?

Dim i As Integer
Dim Quantity As Integer
Dim Counter As Integer

Quantity = Last_Day - First_Day

For i = 1 To Quantity
If Weekday(First_Day) = 7 Or Weekday(First_Day) = 1 Then
Counter = Counter + 1
End If
First_Day = First_Day + 1
Next i

WeekendCount = i - Counter - 1

End Function

#### Erdinç E. Karaçam

##### Board Regular
Code:
``````Function WeekDayCount(First_Day As Date, Last_Day As Date) As Integer

Dim i As Integer
Dim Quantity As Integer
Dim Counter As Integer

Quantity = Last_Day - First_Day

For i = 1 To Quantity
If WeekDay(First_Day) = 7 Or WeekDay(First_Day) = 1 Then
Counter = Counter + 1
End If
First_Day = First_Day + 1
Next i

WeekDayCount = i - Counter

End Function``````

Uups!

Sorry for the mistakes.

And thanks the warning.

UDF should returning for this example: 56 (As a Number Format, not text or date)

Code:
``=WeekDayCount(TODAY();A1)``

Code:
``=WeekDayCount("14/10/2006";"31/12/2006")``

#### hence

##### New Member
okay.. i went tools macros visual basic editor and put your last code in:

Function WeekDayCount(First_Day As Date, Last_Day As Date) As Integer

Dim i As Integer
Dim Quantity As Integer
Dim Counter As Integer

Quantity = Last_Day - First_Day

For i = 1 To Quantity
If Weekday(First_Day) = 7 Or Weekday(First_Day) = 1 Then
Counter = Counter + 1
End If
First_Day = First_Day + 1
Next i

WeekDayCount = i - Counter

End Function

then closed and went back to excel.. then in an empty cell I put in

=WeekDayCount("14/10/2006";"01/12/2006")

but I keep getting an error on the formula.. am I doing something wrong? It keeps hilighting "/2006" from the first date...

sorry!

#### Erdinç E. Karaçam

##### Board Regular
Reason should be location and language settings of your O.S.

I am using Turkish Edition. You can try other arguments.

example as:
=WeekDayCount("14.10.2006","01.12.2006")
or
=WeekDayCount("10.14.2006","12.01.2006")

Try to different ways of arguments until finf corrent syntax.

Take it easy.

Replies
8
Views
275
Replies
1
Views
94
Replies
3
Views
560
Replies
10
Views
1K
Replies
6
Views
896

1,136,654
Messages
5,677,012
Members
419,668
Latest member
DharmaK

### 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.

### Which adblocker are you using?

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