| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I count unique values within a date range?
Hi everyone,
I looked on the forums but was unable to find an instance of what I was looking for. What I have is a spreadsheet that has several columns, in column "A" the are part numbers that repeat depending on the day. in coulmn "B" I have a "yes" if the part is aged over 5 days. In a hidden worksheet I have a list of problem parts to be on the lookout for. What I need to do is have a formula that will count the total number of unique parts over 5 days that is on my problem child parts list. Deepest thanks in advance to all! Sam |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I count unique values within a date range?
Assuming there are no empty cells in the PN range:
PN = range of part numbers Status = range that might equal "Yes" Problems = range on hidden sheet of problem part numbers =SUMPRODUCT(--(MATCH(PN&Status,PN&Status,0)=ROW(PN)-MIN(ROW(PN))+1),--(Status="Yes"),--(ISNUMBER(MATCH(PN,Problems,0)))) Biff "Sam" wrote in message ups.com... Hi everyone, I looked on the forums but was unable to find an instance of what I was looking for. What I have is a spreadsheet that has several columns, in column "A" the are part numbers that repeat depending on the day. in coulmn "B" I have a "yes" if the part is aged over 5 days. In a hidden worksheet I have a list of problem parts to be on the lookout for. What I need to do is have a formula that will count the total number of unique parts over 5 days that is on my problem child parts list. Deepest thanks in advance to all! Sam |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I count unique values within a date range?
On Jun 12, 8:54 pm, "T. Valko" wrote:
Assuming there are no empty cells in the PN range: PN = range of part numbers Status = range that might equal "Yes" Problems = range on hidden sheet of problem part numbers =SUMPRODUCT(--(MATCH(PN&Status,PN&Status,0)=ROW(PN)-MIN(ROW(PN))+1),--(Stat*us="Yes"),--(ISNUMBER(MATCH(PN,Problems,0)))) Biff "Sam" wrote in message ups.com... Hi everyone, I looked on the forums but was unable to find an instance of what I was looking for. What I have is a spreadsheet that has several columns, in column "A" the are part numbers that repeat depending on the day. in coulmn "B" I have a "yes" if the part is aged over 5 days. In a hidden worksheet I have a list of problem parts to be on the lookout for. What I need to do is have a formula that will count the total number of unique parts over 5 days that is on my problem child parts list. Deepest thanks in advance to all! Sam- Hide quoted text - - Show quoted text - Thank you Biff, I havent tried out the formula yet but there are blank rows in the first column. Thanks in advance. Sam |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I count unique values within a date range?
Try this:
=SUMPRODUCT(--(PN<""),--(MATCH(PN&Status,PN&Status,0)=ROW(PN)-MIN(ROW(PN))+1),--(Status="Yes"),--(ISNUMBER(MATCH(PN,Problems,0)))) Biff "Sam" wrote in message oups.com... ********** Thank you Biff, I havent tried out the formula yet but there are blank rows in the first column. Thanks in advance. Sam ********** On Jun 12, 8:54 pm, "T. Valko" wrote: Assuming there are no empty cells in the PN range: PN = range of part numbers Status = range that might equal "Yes" Problems = range on hidden sheet of problem part numbers =SUMPRODUCT(--(MATCH(PN&Status,PN&Status,0)=ROW(PN)-MIN(ROW(PN))+1),--(Stat*us="Yes"),--(ISNUMBER(MATCH(PN,Problems,0)))) Biff "Sam" wrote in message ups.com... Hi everyone, I looked on the forums but was unable to find an instance of what I was looking for. What I have is a spreadsheet that has several columns, in column "A" the are part numbers that repeat depending on the day. in coulmn "B" I have a "yes" if the part is aged over 5 days. In a hidden worksheet I have a list of problem parts to be on the lookout for. What I need to do is have a formula that will count the total number of unique parts over 5 days that is on my problem child parts list. Deepest thanks in advance to all! Sam- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I count unique values within a date range?
On Jun 12, 9:53 pm, "T. Valko" wrote:
Try this: =SUMPRODUCT(--(PN<""),--(MATCH(PN&Status,PN&Status,0)=ROW(PN)-MIN(ROW(PN))*+1),--(Status="Yes"),--(ISNUMBER(MATCH(PN,Problems,0)))) Biff "Sam" wrote in message oups.com... ********** Thank you Biff, I havent tried out the formula yet but there are blank rows in the first column. Thanks in advance. Sam ********** On Jun 12, 8:54 pm, "T. Valko" wrote: Assuming there are no empty cells in the PN range: PN = range of part numbers Status = range that might equal "Yes" Problems = range on hidden sheet of problem part numbers =SUMPRODUCT(--(MATCH(PN&Status,PN&Status,0)=ROW(PN)-MIN(ROW(PN))+1),--(Stat**us="Yes"),--(ISNUMBER(MATCH(PN,Problems,0)))) Biff "Sam" wrote in message oups.com... Hi everyone, I looked on the forums but was unable to find an instance of what I was looking for. What I have is a spreadsheet that has several columns, in column "A" the are part numbers that repeat depending on the day. in coulmn "B" I have a "yes" if the part is aged over 5 days. In a hidden worksheet I have a list of problem parts to be on the lookout for. What I need to do is have a formula that will count the total number of unique parts over 5 days that is on my problem child parts list. Deepest thanks in advance to all! Sam- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Thank you T Valko! and Biff |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I count unique values within a date range?
You're welcome. Thanks for the feedback!
Biff "Sam" wrote in message ups.com... On Jun 12, 9:53 pm, "T. Valko" wrote: Try this: =SUMPRODUCT(--(PN<""),--(MATCH(PN&Status,PN&Status,0)=ROW(PN)-MIN(ROW(PN))*+1),--(Status="Yes"),--(ISNUMBER(MATCH(PN,Problems,0)))) Biff "Sam" wrote in message oups.com... ********** Thank you Biff, I havent tried out the formula yet but there are blank rows in the first column. Thanks in advance. Sam ********** On Jun 12, 8:54 pm, "T. Valko" wrote: Assuming there are no empty cells in the PN range: PN = range of part numbers Status = range that might equal "Yes" Problems = range on hidden sheet of problem part numbers =SUMPRODUCT(--(MATCH(PN&Status,PN&Status,0)=ROW(PN)-MIN(ROW(PN))+1),--(Stat**us="Yes"),--(ISNUMBER(MATCH(PN,Problems,0)))) Biff "Sam" wrote in message oups.com... Hi everyone, I looked on the forums but was unable to find an instance of what I was looking for. What I have is a spreadsheet that has several columns, in column "A" the are part numbers that repeat depending on the day. in coulmn "B" I have a "yes" if the part is aged over 5 days. In a hidden worksheet I have a list of problem parts to be on the lookout for. What I need to do is have a formula that will count the total number of unique parts over 5 days that is on my problem child parts list. Deepest thanks in advance to all! Sam- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Thank you T Valko! and Biff |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Unique Values | Excel Worksheet Functions | |||
| Count text values in a date range | Excel Discussion (Misc queries) | |||
| Count unique values among duplicates in a subtotal range | Excel Discussion (Misc queries) | |||
| Count unique values and create list based on these values | Excel Worksheet Functions | |||
| Count Unique Values | Excel Worksheet Functions | |||
Hybrid Mode
