How do I count unique values within a date range?

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

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
 
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
 
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









- Show quoted text -
Click to expand...

Thank you Biff, I havent tried out the formula yet but there are blank
rows in the first column.

Thanks in advance.

Sam
 
Try this:

=SUMPRODUCT(--(PN<>""),--(MATCH(PN&Status,PN&Status,0)=ROW(PN)-MIN(ROW(PN))+1),--(Status="Yes"),--(ISNUMBER(MATCH(PN,Problems,0))))

Biff

**********
Thank you Biff, I havent tried out the formula yet but there are blank
rows in the first column.

Thanks in advance.

Sam
**********
 
Try this:

=SUMPRODUCT(--(PN<>""),--(MATCH(PN&Status,PN&Status,0)=ROW(PN)-MIN(ROW(PN))­+1),--(Status="Yes"),--(ISNUMBER(MATCH(PN,Problems,0))))

Biff


**********
Thank you Biff, I havent tried out the formula yet but there are blank
rows in the first column.

Thanks in advance.

Sam
**********





- Show quoted text -
Click to expand...


Thank you T Valko! and Biff
 
You're welcome. Thanks for the feedback!

Biff

Try this:

=SUMPRODUCT(--(PN<>""),--(MATCH(PN&Status,PN&Status,0)=ROW(PN)-MIN(ROW(PN))­+1),--(Status="Yes"),--(ISNUMBER(MATCH(PN,Problems,0))))

Biff


**********
Thank you Biff, I havent tried out the formula yet but there are blank
rows in the first column.

Thanks in advance.

Sam
**********





- Show quoted text -
Click to expand...


Thank you T Valko! and Biff
 
Back
Top