Using two conditions in COUNTIF

[hence]

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

 

[Erdinç E. Karaçam]

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:

=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):

=SUM(IF($B$2:$B$5="Y";IF($C$2:$C$5>0,1;1;0);0))

 

[venkat1926]

try this

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

venkat

 

[hence]

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]

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]

Hi,

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

Using of U.D.F.:

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

=WeekendCount(TODAY();A1)

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]

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]

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)

=WeekDayCount(TODAY();A1)

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

 

[hence]

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]

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.