Thread: SUM, IF, Frequency, COUNTIF, AND all rolled into one horrible package.

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:

=SUM(IF(AND(FREQUENCY(A:A,A:A)>0, B:B>01/01/2012, B:B<31/01/2012, C:C="Yes"),1))

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.