COUNTING Help!! with conditions

[juajar]

Hi, I would appreciate it if someone can help me with this.

I have data which looks like the table below. It is a list of all transactions that take place in a month and it can be quite large (7000 rows +-).

Libro1
	A	B	C	D
1	ProductCode	Amount	TransactionDate
2	1	50	25/08/2006
3	3	6	25/08/2006
4	3	20	25/08/2006
5	2	45	26/08/2006
6	1	53	25/08/2006
7	1	46	26/08/2006
8	3	67	26/08/2006
9	2	23	26/08/2006
10	3	34	27/08/2006
Hoja1

What I need to find is, for a given product code, on how many DAYS transactions were made. Notice by the color that there can be MULTIPLE TRANSACTIONS FOR ONE PRODUCT ON A GIVEN DAY so a simple countif will no work. What i am looking for is a formula that will tell me.

PRODUCTCODE ||| DAYS TRADED IN MONTH
Eg. 3 ||| 6 Days

I know a Pivot table can do this but im looking for a formula. Thanks
Tanks in advance for all your help!!!!!

 

[orbea_adam]

You need the number of DIFFERENT days, right?

From your sample data, how do you come up with 6 days for Product Code 3?

 

[juajar]

Example

From your sample data, how do you come up with 6 days for Product Code 3?

It was just an example of what I wanted to get out of the formula. It isn´t 6 really.

 

[lenze]

A Pivot Table will turn this
   A           B      C                
 1 ProductCode Amount Transaction Date 
 2 1           50     25/08/2006       
 3 3           6      25/08/2006       
 4 3           20     25/08/2006       
 5 2           45     26/08/2006       
 6 1           53     25/08/2006       
 7 1           46     26/08/2006       
 8 3           67     26/08/2006       
 9 2           23     26/08/2006       
10 3           34     27/08/2006       

Sheet2
[Table-It] version 05 by Erik Van Geit

into this
   E                         F                G     
13 Count of Transaction Date                        
14 ProductCode               Transaction Date Total 
15 1                         25/08/2006       2     
16                           26/08/2006       1     
17 1 Total                                    3     
18 2                         26/08/2006       2     
19 2 Total                                    2     
20 3                         25/08/2006       2     
21                           26/08/2006       1     
22                           27/08/2006       1     
23 3 Total                                    4     
24 Grand Total                                9     

Sheet2
[Table-It] version 05 by Erik Van Geit

ProductCode in Row Field, Transaction Date in Row field, Count of Transaction Date in Data Field. For such a large table, you may wish to move ProductCode to the Page Field so you can view one product at a time.

lenze

 

[Zack Barresse]

It'd be a complicated formula system with Autofilter. I highly recommend using a pivot table. You can then use GetPivotData formula to retreive that information.

 

[Aladin Akyurek]

Book10
	A	B	C	D	E	F	G
1	ProductCode	Amount	Transaction Date			ProductCode	# Tr Days
2	1	50	8/25/2006			1	2
3	3	6	8/25/2006			3	3
4	3	20	8/25/2006			2	1
5	2	45	8/26/2006
6	1	53	8/25/2006
7	1	46	8/26/2006
8	3	67	8/26/2006
9	2	23	8/26/2006
10	3	34	8/27/2006
11
Sheet1

G2:

=SUM(IF(FREQUENCY(IF($A$2:$A$10=F2,IF(ISNUMBER($C$2:$C$10),MATCH($C$2:$C$10,$C$1:$C$10,0))),ROW($C$2:$C$10)-ROW($C$2)+1),1))

which is confirmed with control+shift+enter (not with enter alone) then copied down.

Alternatively, if you have the morefunc.xll add-in installed...

G2:

=COUNTDIFF(IF($A$2:$A$10=F2,IF(ISNUMBER($C$2:$C$10),$C$2:$C$10)),FALSE,FALSE)

which is also confirmed with control+shift+enter (not with enter alone) then copied down.

 

[juajar]

THANKS

Thanks Aladin, it works like a charm