Count duplicate occurrences based on another column within date range

[chipper49]

I am stuck at adding a date range criteria for the following. Lets say >=5/1/19, <6/1/19 where I can count only those where the shipped status within the date range. It does not matter when the "Finishing" status happened.
I'm trying to wind up with a count of 3. Where the same co # has finishing and shipped status and the status for those 3 is within the date range.

=SUMPRODUCT((MMULT(COUNTIFS(D4:D17,D4:D17,E4:E17,{"Finishing","Shipped"}),{1;1})=2)/COUNTIF(D4:D17,D4:D17))

C D E

Date CO Status
1/1/19
1/2/19 125 Finishing
2/1/19 126 Finishing
2/2/19 127 Finishing
3/1/19 128 Finishing
3/2/19 128 Shipped
5/1/19 125 Shipped
5/13/19 126 Shipped
5/20/19 127 Shipped

[GeoffW283]

Isn't it as simple as:

Please Login or Register  to view this content.

It does at least yield 3 as the answer.

Let me know if I have misunderstood.

[chipper49]

Thanks Geoff, the CO number has to have a Finishing status and a Shipped status listed and the shipped status has to fall within the date range. In the example I'm looking at co 125, 126 and 127 fall within the parameters. Our system does not differentiate between new and refurb units that have shipped. I need to count the ones that have been reworked "Finishing" and shipped to compare to the total shipped for each month.

[GeoffW283]

OK - I think your post #1 formula was the hard part!
Here's a combination of that formula with my post #2 formula that does what I think you want. It produces "3" for your sample data and seems to respond correctly to the best of my understanding to variants of your sample data.

Please Login or Register  to view this content.

Let me know what you think.

[XOR LX]

Hi,

Why have you started a new thread?

https://www.excelforum.com/excel-gen...er-column.html

Regards

[XOR LX]

Ignore that. Just seen Admin's post in that thread.

Regards

[Pepe Le Mokko]

@Xor
Have a look at my post in this link
OP hi jacked then created a new thread. This one can be continued

[XOR LX]

Thanks, Pepe.

3 posts - very confusing!

Cheers

[Pepe Le Mokko]

Thanks, and yes
it took me a while to see what went wrong...

(the pleasures of moderation )

[chipper49]

My apologies for the second thread and thank you for the solution. Just an old guy trying to learn this to pay the bills.

[GeoffW283]

Glad you got your solution in the end!

If you're all set can an you please mark the thread as solved. To do this select Thread Tools from the menu link above your first post and mark this thread as SOLVED. Thanks.