Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria Date Count formula
Hello!
I need further assistance with my previous post. My prior question was the following: I have a spreadsheet that records entry by date added (column A). Each entry can be assigned to at least 1 person (column M). However, there are times when an entry can be assigned to 2 people. The second entry is in column N. Both column M and N use the same drop down lists. I need to count how many items received each month that were assigned to a person (who could be listed in either column M or N). Heres the solution: =SUMPRODUCT((TEXT(A1:A100,"mmyyyy")="102009")*(M1: N100="J Smith")) Unfortunately, I need to add other criteria to the formula. My other criteria is: The spreadsheet has a column that records a completion date(column O). I need a formula that will give me the total number assigned to a person for a month that have not been completed. In addition, I need a formula that gives me the total number assigned to a person for a month that have been completed. Thanks for any help you can give! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria Date Count formula
Try these...
Not completed: =SUMPRODUCT((TEXT(A1:A100,"mmyyyy")="102009")*(M1: N100="J Smith")*(O1:O100="")) Completed: =SUMPRODUCT((TEXT(A1:A100,"mmyyyy")="102009")*(M1: N100="J Smith")*(O1:O100<"")) -- Biff Microsoft Excel MVP "GPearson" wrote in message ... Hello! I need further assistance with my previous post. My prior question was the following: I have a spreadsheet that records entry by date added (column A). Each entry can be assigned to at least 1 person (column M). However, there are times when an entry can be assigned to 2 people. The second entry is in column N. Both column M and N use the same drop down lists. I need to count how many items received each month that were assigned to a person (who could be listed in either column M or N). Here's the solution: =SUMPRODUCT((TEXT(A1:A100,"mmyyyy")="102009")*(M1: N100="J Smith")) Unfortunately, I need to add other criteria to the formula. My other criteria is: The spreadsheet has a column that records a completion date(column O). I need a formula that will give me the total number assigned to a person for a month that have not been completed. In addition, I need a formula that gives me the total number assigned to a person for a month that have been completed. Thanks for any help you can give! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Count dates multiple criteria | Excel Worksheet Functions | |||
Formula to count based on multiple criteria fields w/duplicates | Excel Worksheet Functions | |||
multiple criteria count formula with duplicate data | Excel Worksheet Functions | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
count based on multiple date criteria | Excel Worksheet Functions |