Cross tab query
 

I have a worksheet with 2 columns.

First column contains a date (several duplicates possible, even in hundreds).

Second column contains a code (4 or 5 different values).

Duplicates possible for first column + second column combination. For
example, for a given date, many records possible with same code value.

How could I write a formula to give me per code value the "unique" number of
dates found between 2 given date values. Meaning, as parameter I can pass
three arguments viz.
code value
start date value
end date value

In return I want the number of unique dates (including start and end date)
to be returned.

All help appreciated.

Cross tab query
 

Criterias
In D1: holds code value
In D2: holds start date
In D3: holds end date

=SUM(N(FREQUENCY(IF((Code=D1)*(Date=D2)*(Date<=D3 ),Date),Date)0))

ctrl+shift+enter, not just enter


"DKS" wrote:

I have a worksheet with 2 columns.

First column contains a date (several duplicates possible, even in hundreds).

Second column contains a code (4 or 5 different values).

Duplicates possible for first column + second column combination. For
example, for a given date, many records possible with same code value.

How could I write a formula to give me per code value the "unique" number of
dates found between 2 given date values. Meaning, as parameter I can pass
three arguments viz.
code value
start date value
end date value

In return I want the number of unique dates (including start and end date)
to be returned.

All help appreciated.

Cross tab query
 

I did not understand the last 2 "Date" parameters. What does that refer to?
Should it refer to the column that contains all the dates?

"Teethless mama" wrote:

Criterias
In D1: holds code value
In D2: holds start date
In D3: holds end date

=SUM(N(FREQUENCY(IF((Code=D1)*(Date=D2)*(Date<=D3 ),Date),Date)0))

ctrl+shift+enter, not just enter


"DKS" wrote:

I have a worksheet with 2 columns.

First column contains a date (several duplicates possible, even in hundreds).

Second column contains a code (4 or 5 different values).

Duplicates possible for first column + second column combination. For
example, for a given date, many records possible with same code value.

How could I write a formula to give me per code value the "unique" number of
dates found between 2 given date values. Meaning, as parameter I can pass
three arguments viz.
code value
start date value
end date value

In return I want the number of unique dates (including start and end date)
to be returned.

All help appreciated.

Cross tab query
 

Yes, it is the column. I tried it out and it gives the correct results.
Thanks for your help.

"DKS" wrote:

I did not understand the last 2 "Date" parameters. What does that refer to?
Should it refer to the column that contains all the dates?

"Teethless mama" wrote:

Criterias
In D1: holds code value
In D2: holds start date
In D3: holds end date

=SUM(N(FREQUENCY(IF((Code=D1)*(Date=D2)*(Date<=D3 ),Date),Date)0))

ctrl+shift+enter, not just enter


"DKS" wrote:

I have a worksheet with 2 columns.

First column contains a date (several duplicates possible, even in hundreds).

Second column contains a code (4 or 5 different values).

Duplicates possible for first column + second column combination. For
example, for a given date, many records possible with same code value.

How could I write a formula to give me per code value the "unique" number of
dates found between 2 given date values. Meaning, as parameter I can pass
three arguments viz.
code value
start date value
end date value

In return I want the number of unique dates (including start and end date)
to be returned.

All help appreciated.