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

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

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

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 -

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

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