Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Single statement database/range processing with criteria | Excel Worksheet Functions | |||
COUNTIF with two criteria | Excel Worksheet Functions | |||
Can criteria in countif statement be a color? | Excel Worksheet Functions | |||
Countif w/ Multiple Criteria | Excel Worksheet Functions | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions |