COUNTIF statement with 3 criteria checks

[Malvaro]

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 ftry and igure 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....

[bgeier]

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

[Malvaro]

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="Closed"))

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]

*bump for one more piece of help*

[Domenic]

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:F10000>1),--(F5:F10000<5))

Hope this helps!

In article <[email protected]>,
Malvaro <[email protected]> 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....