# COUNTING Help!! with conditions

#### juajar

##### New Member
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
ABCD
1ProductCodeAmountTransactionDate
215025/08/2006
33625/08/2006
432025/08/2006
524526/08/2006
615325/08/2006
714626/08/2006
836726/08/2006
922326/08/2006
1033427/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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

##### Well-known Member
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

##### New Member
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

##### Legend
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

##### MrExcel MVP
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.

##### MrExcel MVP
Book10
ABCDEFG
1ProductCodeAmountTransaction DateProductCode# Tr Days
21508/25/200612
3368/25/200633
43208/25/200621
52458/26/2006
61538/25/2006
71468/26/2006
83678/26/2006
92238/26/2006
103348/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

##### New Member
THANKS

Thanks Aladin, it works like a charm

Replies
1
Views
202
Replies
3
Views
412
Replies
10
Views
345
Replies
2
Views
102
Replies
1
Views
914

1,195,619
Messages
6,010,736
Members
441,567
Latest member
Flitbee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back