Sumproduct

E

Excel 2007 - SPB

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
 
D

Don Guillett

One way
=if(SUMPRODUCT(--(TEXT(LDatein,"mmyy")=TEXT($FM$4,"mmyy"))*(LPilot="SPB"))>0,1,"")--Don GuillettMicrosoft MVP ExcelSalesAid [email protected]"Excel 2007 - SPB" <[email protected]> wrote in messagehave 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>>
 
E

Excel 2007 - SPB

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

Joel

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.
 
E

Excel 2007 - SPB

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
 
S

Sandy Mann

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

The *Helper Column* can of course be hidden.

--
HTH

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
J

Joel

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"
 
T

T. Valko

Try this array formula** :

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

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

Excel 2007 - SPB

Thanks , works perfectly!

T. Valko said:
Try this array formula** :

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

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