unique count with conditions

[jogre]

Hi,

I am trying to do the following with the below data:

For a specific date, I need to calculate the number of transactions. A
transaction ID can appear more than once if there are several items
sold during this transaction.
for the 05/12/2006 the answer should be 3 (transaction ID 4, 6 & 8)
and the 5 if date was the 6th and 4 if the date was the 7th?

I have this

=SUM(1*(FREQUENCY(C2:C21,C2:C21)>0))

to caculate the unique transactions (12)and

=COUNTIF(D221,"05/12/2006")

to tell me there are 8 rows for the 5th but I struggle to get the
intersection of the 2.


Price Paid Item Ref Transaction ID Date
5 PPC003 4 05/12/2006
0 PPC001 4 05/12/2006
100 PPC004 6 05/12/2006
200 PPC004 6 05/12/2006
100 TEL102 8 05/12/2006
5 PPC003 8 05/12/2006
0 PPC001 8 05/12/2006
1000 PPC004 8 05/12/2006
55 PPC004 13 06/12/2006
99.99 PPC004 15 06/12/2006
30 PPC004 16 06/12/2006
40 PPC004 17 06/12/2006
20.99 PPC004 18 06/12/2006
50 PPC004 19 07/12/2006
100 PPC004 21 07/12/2006
100 PPC004 21 07/12/2006
120 PPC004 23 07/12/2006
150 PPC004 23 07/12/2006
250 PPC004 23 07/12/2006
200 PPC004 26 07/12/2006

 

[Bob Phillips]

=COUNT(1/IF((D221=--"2006-12-05"),MATCH($B$2:$B$21,$B$2:$B$21,0)=ROW($B$2:$B$21)-ROW($B$2)+1))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Lori]

With the criteria date in E2 e.g. 05/12/2006, try this array-formula
(CSE)

=SUM(1*(FREQUENCY(IF(D221=E2,C2:C21),C2:C21)>0))

 

[jogre]

Thanks BOB.

I found the answer by surfing a little bit and got the following:


=SUM(IF(FREQUENCY(IF((C2:C21<>"")*(D21="05/12/2006"
+0),MATCH(C2:C21,C2:C21,0)),ROW(INDIRECT("1:"&ROWS(C2:C21))))>0,1,0))

 

[jogre]

I now need to do the same thing but instead of counting the number of
transactions, I need to sum the value (Price paid) of these unique
transactions?

Thanks

 

[Bob Phillips]

How would you decide which of two similar items which price you would use in
the sum?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)