Sumproduct
 

I have the following formula

=SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot= "SPB"))
Ldatein is a column with a date entry, FM4 is a date, LPilot is a 3 letter id

This works fine, but How do I "Count" dates ONCE i.e. two entries on 6/1
would only count 1 entry.

Thanks for any help

SPB

Sumproduct
 

One way
=if(SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot= "SPB"))0,1,"")--Don GuillettMicrosoft MVP ExcelSalesAid "Excel 2007 - SPB" wrote in ...I have the following formula =SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot= "SPB")) Ldatein is a column with a date entry, FM4 is a date, LPilot is a 3 letterid This works fine, but How do I "Count" dates ONCE i.e. two entries on 6/1 would only count 1 entry. Thanks for any help SPB

Sumproduct
 

This just gives me a blank or a 1 not a total of the orrurances of the dates
what have "SPB"
any other ideas

"Don Guillett" wrote:

One way
=if(SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot= "SPB"))0,1,"")--Don GuillettMicrosoft MVP ExcelSalesAid "Excel 2007 - SPB" wrote in ...I have the following formula =SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot= "SPB")) Ldatein is a column with a date entry, FM4 is a date, LPilot is a 3 letterid This works fine, but How do I "Count" dates ONCE i.e. two entries on 6/1 would only count 1 entry. Thanks for any help SPB

Sumproduct
 

Your code should count multiple dates without any changes provided the number
of rows in LDatein and the number of rows in LPilot covers the number of rows
you want to add.

"Excel 2007 - SPB" wrote:

I have the following formula

=SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot= "SPB"))
Ldatein is a column with a date entry, FM4 is a date, LPilot is a 3 letter id

This works fine, but How do I "Count" dates ONCE i.e. two entries on 6/1
would only count 1 entry.

Thanks for any help

SPB

Sumproduct
 

Thanks

The formula does count, but it counts all the entries (5 in the example below)
Yes, Both columns have the same number of rows
I would like the count to only count on instance of a date whick matches the
Lpilot column
i.e.
date Lpilot
6/1/08 spb
6/1/08 spb
6/2/08 spb
6/2/08 spb
6/3/08 spb

should = 3

"Joel" wrote:

Your code should count multiple dates without any changes provided the number
of rows in LDatein and the number of rows in LPilot covers the number of rows
you want to add.

"Excel 2007 - SPB" wrote:

I have the following formula

=SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot= "SPB"))
Ldatein is a column with a date entry, FM4 is a date, LPilot is a 3 letter id

This works fine, but How do I "Count" dates ONCE i.e. two entries on 6/1
would only count 1 entry.

Thanks for any help

SPB

Sumproduct
 

I would use a Helper Column - say Column C with the formula in C2:

=COUNTIF($A$2:A2,A2)

Copied down on the fill handle to C6. This will produce a 1 for the 1st
instance of any date. and a larger number for subsequent duplicates.

Then the formula:

=SUMPRODUCT(((TEXT(A2:A6,"mmyy")=TEXT(D1,"mmyy"))* (B2:B6="spb")*(C2:C6=1)))

Returns 3

Substitute your range names for the ranges.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Excel 2007 - SPB" wrote in message
...
Thanks

The formula does count, but it counts all the entries (5 in the example
below)
Yes, Both columns have the same number of rows
I would like the count to only count on instance of a date whick matches
the
Lpilot column
i.e.
date Lpilot
6/1/08 spb
6/1/08 spb
6/2/08 spb
6/2/08 spb
6/3/08 spb

should = 3

"Joel" wrote:

Your code should count multiple dates without any changes provided the
number
of rows in LDatein and the number of rows in LPilot covers the number of
rows
you want to add.

"Excel 2007 - SPB" wrote:

I have the following formula

=SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot= "SPB"))
Ldatein is a column with a date entry, FM4 is a date, LPilot is a 3
letter id

This works fine, but How do I "Count" dates ONCE i.e. two entries on
6/1
would only count 1 entry.

Thanks for any help

SPB

Sumproduct
 

The *Helper Column* can of course be hidden.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
I would use a Helper Column - say Column C with the formula in C2:

=COUNTIF($A$2:A2,A2)

Copied down on the fill handle to C6. This will produce a 1 for the 1st
instance of any date. and a larger number for subsequent duplicates.

Then the formula:

=SUMPRODUCT(((TEXT(A2:A6,"mmyy")=TEXT(D1,"mmyy"))* (B2:B6="spb")*(C2:C6=1)))

Returns 3

Substitute your range names for the ranges.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Excel 2007 - SPB" wrote in
message ...
Thanks

The formula does count, but it counts all the entries (5 in the example
below)
Yes, Both columns have the same number of rows
I would like the count to only count on instance of a date whick matches
the
Lpilot column
i.e.
date Lpilot
6/1/08 spb
6/1/08 spb
6/2/08 spb
6/2/08 spb
6/3/08 spb

should = 3

"Joel" wrote:

Your code should count multiple dates without any changes provided the
number
of rows in LDatein and the number of rows in LPilot covers the number of
rows
you want to add.

"Excel 2007 - SPB" wrote:

I have the following formula

=SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot= "SPB"))
Ldatein is a column with a date entry, FM4 is a date, LPilot is a 3
letter id

This works fine, but How do I "Count" dates ONCE i.e. two entries on
6/1
would only count 1 entry.

Thanks for any help

SPB

Sumproduct
 

You had a very simple error. Your dates are "mmyy" which is why you are
getting a 5. the code is only looking at month and year

replace in two places
from
" mmyy"
to
"ddmmyy"

"Excel 2007 - SPB" wrote:

Thanks

The formula does count, but it counts all the entries (5 in the example below)
Yes, Both columns have the same number of rows
I would like the count to only count on instance of a date whick matches the
Lpilot column
i.e.
date Lpilot
6/1/08 spb
6/1/08 spb
6/2/08 spb
6/2/08 spb
6/3/08 spb

should = 3

"Joel" wrote:

Your code should count multiple dates without any changes provided the number
of rows in LDatein and the number of rows in LPilot covers the number of rows
you want to add.

"Excel 2007 - SPB" wrote:

I have the following formula

=SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot= "SPB"))
Ldatein is a column with a date entry, FM4 is a date, LPilot is a 3 letter id

This works fine, but How do I "Count" dates ONCE i.e. two entries on 6/1
would only count 1 entry.

Thanks for any help

SPB

Sumproduct
 

Try this array formula** :

=COUNT(1/FREQUENCY(IF((TEXT(LDatein,"mmyy")=TEXT(FM4,"mmyy" ))*(LPilot="SPB"),MATCH(LDatein,LDatein,0)),ROW(LD atein)-MIN(ROW(LDatein))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Excel 2007 - SPB" wrote in message
...
Thanks

The formula does count, but it counts all the entries (5 in the example
below)
Yes, Both columns have the same number of rows
I would like the count to only count on instance of a date whick matches
the
Lpilot column
i.e.
date Lpilot
6/1/08 spb
6/1/08 spb
6/2/08 spb
6/2/08 spb
6/3/08 spb

should = 3

"Joel" wrote:

Your code should count multiple dates without any changes provided the
number
of rows in LDatein and the number of rows in LPilot covers the number of
rows
you want to add.

"Excel 2007 - SPB" wrote:

I have the following formula

=SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot= "SPB"))
Ldatein is a column with a date entry, FM4 is a date, LPilot is a 3
letter id

This works fine, but How do I "Count" dates ONCE i.e. two entries on
6/1
would only count 1 entry.

Thanks for any help

SPB

Sumproduct
 

Thanks , works perfectly!

"T. Valko" wrote:

Try this array formula** :

=COUNT(1/FREQUENCY(IF((TEXT(LDatein,"mmyy")=TEXT(FM4,"mmyy" ))*(LPilot="SPB"),MATCH(LDatein,LDatein,0)),ROW(LD atein)-MIN(ROW(LDatein))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Excel 2007 - SPB" wrote in message
...
Thanks

The formula does count, but it counts all the entries (5 in the example
below)
Yes, Both columns have the same number of rows
I would like the count to only count on instance of a date whick matches
the
Lpilot column
i.e.
date Lpilot
6/1/08 spb
6/1/08 spb
6/2/08 spb
6/2/08 spb
6/3/08 spb

should = 3

"Joel" wrote:

Your code should count multiple dates without any changes provided the
number
of rows in LDatein and the number of rows in LPilot covers the number of
rows
you want to add.

"Excel 2007 - SPB" wrote:

I have the following formula

=SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot= "SPB"))
Ldatein is a column with a date entry, FM4 is a date, LPilot is a 3
letter id

This works fine, but How do I "Count" dates ONCE i.e. two entries on
6/1
would only count 1 entry.

Thanks for any help

SPB

Sumproduct
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Excel 2007 - SPB" wrote in message
...
Thanks , works perfectly!

"T. Valko" wrote:

Try this array formula** :

=COUNT(1/FREQUENCY(IF((TEXT(LDatein,"mmyy")=TEXT(FM4,"mmyy" ))*(LPilot="SPB"),MATCH(LDatein,LDatein,0)),ROW(LD atein)-MIN(ROW(LDatein))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Excel 2007 - SPB" wrote in
message
...
Thanks

The formula does count, but it counts all the entries (5 in the example
below)
Yes, Both columns have the same number of rows
I would like the count to only count on instance of a date whick
matches
the
Lpilot column
i.e.
date Lpilot
6/1/08 spb
6/1/08 spb
6/2/08 spb
6/2/08 spb
6/3/08 spb

should = 3

"Joel" wrote:

Your code should count multiple dates without any changes provided the
number
of rows in LDatein and the number of rows in LPilot covers the number
of
rows
you want to add.

"Excel 2007 - SPB" wrote:

I have the following formula

=SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot= "SPB"))
Ldatein is a column with a date entry, FM4 is a date, LPilot is a 3
letter id

This works fine, but How do I "Count" dates ONCE i.e. two entries on
6/1
would only count 1 entry.

Thanks for any help

SPB