Using two conditions in COUNTIF

hence

New Member
Joined
Oct 13, 2006
Messages
3
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
Joined
Sep 23, 2006
Messages
202
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))
 

hence

New Member
Joined
Oct 13, 2006
Messages
3
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
Joined
Aug 21, 2005
Messages
4,824

ADVERTISEMENT

see excel help "networkdays worksheet function"
hope "analysis toll pak" add in is available in your excel. see tools(menu bar) -add-ins
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
Joined
Sep 23, 2006
Messages
202
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
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

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
Joined
Sep 23, 2006
Messages
202
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. :oops:

And thanks the warning. :biggrin:

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
Joined
Oct 13, 2006
Messages
3
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
Joined
Sep 23, 2006
Messages
202
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.
 

Forum statistics

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