Sum of unique items in a list matching certain criteria


R

Riddler

I have a database query that returns some production records data. I
am trying to calculate the number of unique orders that occur within
each hour. The formula below gives me the number of records(parts)
that were run between a time interval like C37 to C38 which could be
6:00am to 7:00 am. My problem is just returning the unique ones that
fit in this time frame. The part numbers can look like:
A,B,C,D,D,D,D,E,F,G,H. They will rerun a part sometimes if it comes
out bad (part D). I only want to count part "D" once though. (My order
number are actualy like (05084001))


=SUMPRODUCT((VALUE('Production Records Link'!$D$5:$D$10000)>=Sheet1!
C37)*(VALUE('Production Records Link'!$D$5:$D$10000)<Sheet1!C38)*1)

Is there any way to be able to only count the unique items that are in
the time range specified like in the formula above. I would like to
stay with a formula solution if possible. I can write a VBA solution
if there is no other way but would like to not have to.

Thanks
Scott
 
Ad

Advertisements

A

AndrewArmstrong

=SUM(IF("your range"="D","value if true","value if false"))
This is an array formula press CTRL+SHIFT+ENTER after entering the
formula
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top