COUNTIF statement with 3 criteria checks
 

I am trying to create a COUNTIF statement that will reference
three columns (random dates, repetitive names, repetitive text)
and have three criteria checks. For example:

Column A: May 1, May 15, *June 1*
Column B: Bob, Tom, *Bob*
Column C: Pending, Closed, *Closed*

The rows would associate May 1 -- Bob -- Pending together along
with an unknown number of total rows.

The target cell with the criteria checks will need:
a) to reference TODAY() and pass if between 1-5 days,
b) the criteria will only pass if persons name is "Bob", and
c) the project status has to be "Closed"

if any of these criteria fail, the row cannot be counted. In this
example,
row 3: June 1, Bob, Closed passed all three and would be counted.

I know the coding is incorrect, but I'm really banging my head against
the
wall to figure out -- and this is the closest I can give for an
example:

=countif((countif(A:A, "1")+countif(A:A,,"<6")) AND
countif(B:B,"*BOB") AND countif (C:C,"*Closed"))


Please help me.... I'm losing my mind.... :)


--
Malvaro
------------------------------------------------------------------------
Malvaro's Profile: http://www.excelforum.com/member.php...o&userid=29589
View this thread: http://www.excelforum.com/showthread...hreadid=547943

COUNTIF statement with 3 criteria checks
 

Did you enter the formula as an array formula <ctrl<shift<enter
instead of just <enter?

You could also try sumproduct, or countif nested with if statements


--
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
View this thread: http://www.excelforum.com/showthread...hreadid=547943

COUNTIF statement with 3 criteria checks
 

bgeier Wrote:
Did you enter the formula as an array formula <ctrl<shift<enter
instead of just <enter?

You could also try sumproduct, or countif nested with if statements

I was digging around and found a winner using the SUMPRODUCT, so
now I got the Column B & C working correctly:

=SUMPRODUCT((D5:D10000="BOB")*(E5:E10000="To be Sent to Carrier
(TCAR)"))

I now need to correct the final criteria which sets the date range, so
this
can be inserted into the above working formula:

the end of working formula...
"*(NETWORKDAYS(C5:C10000,TODAY())1)*(NETWORKDAYS( C5:C10000,TODAY())<5))"

I'm currently getting the #VALUE error....


--
Malvaro
------------------------------------------------------------------------
Malvaro's Profile: http://www.excelforum.com/member.php...o&userid=29589
View this thread: http://www.excelforum.com/showthread...hreadid=547943

COUNTIF statement with 3 criteria checks
 

*bump for one more piece of help*


--
Malvaro
------------------------------------------------------------------------
Malvaro's Profile: http://www.excelforum.com/member.php...o&userid=29589
View this thread: http://www.excelforum.com/showthread...hreadid=547943

COUNTIF statement with 3 criteria checks
 

NETWORKDAYS does not work with arrays. Try using a helper column. For
example, enter the following formula in a column, let's say Column F...

F5, copied down:

=NETWORKDAYS(C5,TODAY())

Then, try the following formula...

=SUMPRODUCT(--(D5:D10000="BOB"),--(E5:E10000="To be Sent to Carrier
(TCAR)"),--(F5:F100001),--(F5:F10000<5))

Hope this helps!

In article ,
Malvaro wrote:

I was digging around and found a winner using the SUMPRODUCT, so
now I got the Column B & C working correctly:

=SUMPRODUCT((D5:D10000="BOB")*(E5:E10000="To be Sent to Carrier
(TCAR)"))

I now need to correct the final criteria which sets the date range, so
this
can be inserted into the above working formula:

the end of working formula...
"*(NETWORKDAYS(C5:C10000,TODAY())1)*(NETWORKDAYS( C5:C10000,TODAY())<5))"

I'm currently getting the #VALUE error....