Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
If and Sumproduct | Excel Discussion (Misc queries) | |||
sumproduct? | Excel Worksheet Functions | |||
SUMPRODUCT | Excel Worksheet Functions |