Hello all you fine ladies and gentlemen. I have a problem that has given me much grief lately.
I'm trying to count occurrences of values within a given set of data, with a few additional criteria for other values on the same row. Here an example of what I'm trying to do:
|
A |
B |
C |
1 |
Client Identifier |
Parcel Creation Date |
Parcel Received by Client |
2 |
8903 |
11/01/2012 |
Yes |
3 |
8904 |
12/12/2011 |
Yes |
3 |
8902 |
16/01/2012 |
Yes |
4 |
8903 |
4/01/2012 |
Yes |
5 |
8909 |
19/01/2012 |
No |
6 |
8908 |
23/01/2012 |
Yes |
7 |
8910 |
7/01/2012 |
Yes |
8 |
8903 |
29/01/2012 |
No |
9 |
8907 |
4/02/2012 |
Yes |
How would one go about finding a single value for:- Unique Client ID individuals
- Whom have parcels made within January 2012
- And have also received their parcel?
The value that I am looking to come up with, for this example, would be 4:- Client 8903 is counted once, despite having multiple orders.
- Client 8904 is not counted, as the client's parcel was made in December 2011
- Client 8902 is counted once
- Client 8909 is not counted, as the client's parcel was not received
- Clients 8908 and 8910 are each counted once
- Client 8903 is not counted (in row 8) as the client's parcel was not received
- Client 8907 is not counted, as the client's parcel was made in February 2012
Here was my failed attempt at trying to solve this:
The above code gives a value of 0. I'm really stumped at how to solve this.
Bonus points for not using any filters, and only solving this problem with excel formulae. This is because I have a high amount of rows to filter (about 10k rows).
Thank you for reading this far! Please let me know if what I'm trying to do is impossible. I feel like it shouldn't be, but alas.
Bookmarks