sumproduct to create a unique list
 

Thanks in advance, I can not get my head around the sumproduct

Database seperate sheet "DATA"
C column =date formated as mm/dd/yy
F column = Aircraft No ei, N123AB

I am trying to product a unique list of aircraft used for the month

I have the following but need to add the date element

=IF(SUMPRODUCT((Data!$F$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$F$3000,$V$129:$V129,0)),0 ),1))

The V129 is where I want to start the list I estimate I will need place ment
for about 10 aircraft max

sumproduct to create a unique list
 

Let's say your aircraft names are in A1:A100,Dates are in B1:B100. I'm
assuming you are looking for "Aircraft Name" for March 2007.

=SUMPRODUCT(--(A1:A100="Aircraft
Name'),--(Year(B1:B100)=2007),--(Month(B:B100)=3))

HTH,
Barb Reinhardt



"Excel 2003 - SPB" wrote:

Thanks in advance, I can not get my head around the sumproduct

Database seperate sheet "DATA"
C column =date formated as mm/dd/yy
F column = Aircraft No ei, N123AB

I am trying to product a unique list of aircraft used for the month

I have the following but need to add the date element

=IF(SUMPRODUCT((Data!$F$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$F$3000,$V$129:$V129,0)),0 ),1))

The V129 is where I want to start the list I estimate I will need place ment
for about 10 aircraft max

sumproduct to create a unique list
 

It looks like you want to extract unique tail numbers in a certain date
range.

Are there any empty/blank cells in either the date range or the tail number
range?

The date range of interest, do you want just a specific month or do you want
the specific month of a specific year?

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in message
...
Thanks in advance, I can not get my head around the sumproduct

Database seperate sheet "DATA"
C column =date formated as mm/dd/yy
F column = Aircraft No ei, N123AB

I am trying to product a unique list of aircraft used for the month

I have the following but need to add the date element

=IF(SUMPRODUCT((Data!$F$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$F$3000,$V$129:$V129,0)),0 ),1))

The V129 is where I want to start the list I estimate I will need place
ment
for about 10 aircraft max

sumproduct to create a unique list
 

unique tail numbers by month only, The rest of the report used a cell ref for
the date
the previous post did not work, thanks for any suggestions.
SPB

"T. Valko" wrote:

It looks like you want to extract unique tail numbers in a certain date
range.

Are there any empty/blank cells in either the date range or the tail number
range?

The date range of interest, do you want just a specific month or do you want
the specific month of a specific year?

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in message
...
Thanks in advance, I can not get my head around the sumproduct

Database seperate sheet "DATA"
C column =date formated as mm/dd/yy
F column = Aircraft No ei, N123AB

I am trying to product a unique list of aircraft used for the month

I have the following but need to add the date element

=IF(SUMPRODUCT((Data!$F$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$F$3000,$V$129:$V129,0)),0 ),1))

The V129 is where I want to start the list I estimate I will need place
ment
for about 10 aircraft max

sumproduct to create a unique list
 

Are there any empty/blank cells in either the date range or the tail
number
range?

Well?

The answer to that question can either add complexity of eliminate unneeded
complexity!

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in message
...
unique tail numbers by month only, The rest of the report used a cell ref
for
the date
the previous post did not work, thanks for any suggestions.
SPB

"T. Valko" wrote:

It looks like you want to extract unique tail numbers in a certain date
range.

Are there any empty/blank cells in either the date range or the tail
number
range?

The date range of interest, do you want just a specific month or do you
want
the specific month of a specific year?

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in
message
...
Thanks in advance, I can not get my head around the sumproduct

Database seperate sheet "DATA"
C column =date formated as mm/dd/yy
F column = Aircraft No ei, N123AB

I am trying to product a unique list of aircraft used for the month

I have the following but need to add the date element

=IF(SUMPRODUCT((Data!$F$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$F$3000,$V$129:$V129,0)),0 ),1))

The V129 is where I want to start the list I estimate I will need place
ment
for about 10 aircraft max

sumproduct to create a unique list
 

no blank cells in either columns
spb


"T. Valko" wrote:

Are there any empty/blank cells in either the date range or the tail
number
range?

Well?

The answer to that question can either add complexity of eliminate unneeded
complexity!

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in message
...
unique tail numbers by month only, The rest of the report used a cell ref
for
the date
the previous post did not work, thanks for any suggestions.
SPB

"T. Valko" wrote:

It looks like you want to extract unique tail numbers in a certain date
range.

Are there any empty/blank cells in either the date range or the tail
number
range?

The date range of interest, do you want just a specific month or do you
want
the specific month of a specific year?

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in
message
...
Thanks in advance, I can not get my head around the sumproduct

Database seperate sheet "DATA"
C column =date formated as mm/dd/yy
F column = Aircraft No ei, N123AB

I am trying to product a unique list of aircraft used for the month

I have the following but need to add the date element

=IF(SUMPRODUCT((Data!$F$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$F$3000,$V$129:$V129,0)),0 ),1))

The V129 is where I want to start the list I estimate I will need place
ment
for about 10 aircraft max

sumproduct to create a unique list
 

Recap
no blank cells in either coulmn
based on a cell address of J4 Need the results from a choosen month
to create a unique list of aircraft used in that month

SPB

"T. Valko" wrote:

Are there any empty/blank cells in either the date range or the tail
number
range?

Well?

The answer to that question can either add complexity of eliminate unneeded
complexity!

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in message
...
unique tail numbers by month only, The rest of the report used a cell ref
for
the date
the previous post did not work, thanks for any suggestions.
SPB

"T. Valko" wrote:

It looks like you want to extract unique tail numbers in a certain date
range.

Are there any empty/blank cells in either the date range or the tail
number
range?

The date range of interest, do you want just a specific month or do you
want
the specific month of a specific year?

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in
message
...
Thanks in advance, I can not get my head around the sumproduct

Database seperate sheet "DATA"
C column =date formated as mm/dd/yy
F column = Aircraft No ei, N123AB

I am trying to product a unique list of aircraft used for the month

I have the following but need to add the date element

=IF(SUMPRODUCT((Data!$F$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$F$3000,$V$129:$V129,0)),0 ),1))

The V129 is where I want to start the list I estimate I will need place
ment
for about 10 aircraft max

sumproduct to create a unique list
 

Ok...

Both of these formulas are array formulas**.

Use a separate cell to get the total count of tail numbers that meet the
criteria.

Date = named range for your date column
TN = (Tail Number) named range for the acft tail numbers

Count of distinct tail numbers for the month of 5 (May):

=COUNT(1/FREQUENCY(IF(MONTH(Date)=5,MATCH(TN,TN,0)),ROW(TN)-MIN(ROW(TN)+1)))

Assume the above formula is entered in cell G1.

Formula to extract distinct tail numbers:

=IF(ROWS($1:1)<=G$1,INDEX(TN,SMALL(IF(MONTH(Date)= 5,IF(MATCH(MONTH(date)&TN,MONTH(date)&TN,0)=ROW(TN )-MIN(ROW(TN))+1,ROW(TN)-MIN(ROW(TN))+1)),ROWS($1:1))),"")

If the first row of your ranges is set and will not change we can shorten
this a little. For example, your posted formula uses references to ranges
that start in row 2. In the above formula we calculate an offset from the
starting row so that this formula can be used by anyone. In other words,
it's generic. We can shorten it a little by hardcoding this offset rather
than calulating it. So, if the data ranges start on row 2 we can this
version:

=IF(ROWS($1:1)<=G$1,INDEX(TN,SMALL(IF(MONTH(date)= 5,IF(MATCH(MONTH(date)&TN,MONTH(date)&TN,0)=ROW(TN )-1,ROW(TN)-1)),ROWS($1:1))),"")

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

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in message
...

Recap
no blank cells in either coulmn
based on a cell address of J4 Need the results from a choosen month
to create a unique list of aircraft used in that month

SPB

"T. Valko" wrote:

Are there any empty/blank cells in either the date range or the tail
number
range?

Well?

The answer to that question can either add complexity of eliminate
unneeded
complexity!

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in
message
...
unique tail numbers by month only, The rest of the report used a cell
ref
for
the date
the previous post did not work, thanks for any suggestions.
SPB

"T. Valko" wrote:

It looks like you want to extract unique tail numbers in a certain
date
range.

Are there any empty/blank cells in either the date range or the tail
number
range?

The date range of interest, do you want just a specific month or do
you
want
the specific month of a specific year?

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in
message
...
Thanks in advance, I can not get my head around the sumproduct

Database seperate sheet "DATA"
C column =date formated as mm/dd/yy
F column = Aircraft No ei, N123AB

I am trying to product a unique list of aircraft used for the month

I have the following but need to add the date element

=IF(SUMPRODUCT((Data!$F$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$F$3000,$V$129:$V129,0)),0 ),1))

The V129 is where I want to start the list I estimate I will need
place
ment
for about 10 aircraft max

sumproduct to create a unique list
 

Away for a few days
Works great and I am starting to understand it a little, used a simular
formula in another area of the spreadsheet and changed the date ref to
--(TEXT(Login!$E$2:$E$2972,"ddmmyy")=TEXT($A8,"ddmmy y")) works well
Thanks again

SPB

"T. Valko" wrote:

Ok...

Both of these formulas are array formulas**.

Use a separate cell to get the total count of tail numbers that meet the
criteria.

Date = named range for your date column
TN = (Tail Number) named range for the acft tail numbers

Count of distinct tail numbers for the month of 5 (May):

=COUNT(1/FREQUENCY(IF(MONTH(Date)=5,MATCH(TN,TN,0)),ROW(TN)-MIN(ROW(TN)+1)))

Assume the above formula is entered in cell G1.

Formula to extract distinct tail numbers:

=IF(ROWS($1:1)<=G$1,INDEX(TN,SMALL(IF(MONTH(Date)= 5,IF(MATCH(MONTH(date)&TN,MONTH(date)&TN,0)=ROW(TN )-MIN(ROW(TN))+1,ROW(TN)-MIN(ROW(TN))+1)),ROWS($1:1))),"")

If the first row of your ranges is set and will not change we can shorten
this a little. For example, your posted formula uses references to ranges
that start in row 2. In the above formula we calculate an offset from the
starting row so that this formula can be used by anyone. In other words,
it's generic. We can shorten it a little by hardcoding this offset rather
than calulating it. So, if the data ranges start on row 2 we can this
version:

=IF(ROWS($1:1)<=G$1,INDEX(TN,SMALL(IF(MONTH(date)= 5,IF(MATCH(MONTH(date)&TN,MONTH(date)&TN,0)=ROW(TN )-1,ROW(TN)-1)),ROWS($1:1))),"")

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

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in message
...

Recap
no blank cells in either coulmn
based on a cell address of J4 Need the results from a choosen month
to create a unique list of aircraft used in that month

SPB

"T. Valko" wrote:

Are there any empty/blank cells in either the date range or the tail
number
range?

Well?

The answer to that question can either add complexity of eliminate
unneeded
complexity!

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in
message
...
unique tail numbers by month only, The rest of the report used a cell
ref
for
the date
the previous post did not work, thanks for any suggestions.
SPB

"T. Valko" wrote:

It looks like you want to extract unique tail numbers in a certain
date
range.

Are there any empty/blank cells in either the date range or the tail
number
range?

The date range of interest, do you want just a specific month or do
you
want
the specific month of a specific year?

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in
message
...
Thanks in advance, I can not get my head around the sumproduct

Database seperate sheet "DATA"
C column =date formated as mm/dd/yy
F column = Aircraft No ei, N123AB

I am trying to product a unique list of aircraft used for the month

I have the following but need to add the date element

=IF(SUMPRODUCT((Data!$F$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$F$3000,$V$129:$V129,0)),0 ),1))

The V129 is where I want to start the list I estimate I will need
place
ment
for about 10 aircraft max

sumproduct to create a unique list
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in message
...
Away for a few days
Works great and I am starting to understand it a little, used a simular
formula in another area of the spreadsheet and changed the date ref to
--(TEXT(Login!$E$2:$E$2972,"ddmmyy")=TEXT($A8,"ddmmy y")) works well
Thanks again

SPB

"T. Valko" wrote:

Ok...

Both of these formulas are array formulas**.

Use a separate cell to get the total count of tail numbers that meet the
criteria.

Date = named range for your date column
TN = (Tail Number) named range for the acft tail numbers

Count of distinct tail numbers for the month of 5 (May):

=COUNT(1/FREQUENCY(IF(MONTH(Date)=5,MATCH(TN,TN,0)),ROW(TN)-MIN(ROW(TN)+1)))

Assume the above formula is entered in cell G1.

Formula to extract distinct tail numbers:

=IF(ROWS($1:1)<=G$1,INDEX(TN,SMALL(IF(MONTH(Date)= 5,IF(MATCH(MONTH(date)&TN,MONTH(date)&TN,0)=ROW(TN )-MIN(ROW(TN))+1,ROW(TN)-MIN(ROW(TN))+1)),ROWS($1:1))),"")

If the first row of your ranges is set and will not change we can shorten
this a little. For example, your posted formula uses references to ranges
that start in row 2. In the above formula we calculate an offset from the
starting row so that this formula can be used by anyone. In other words,
it's generic. We can shorten it a little by hardcoding this offset rather
than calulating it. So, if the data ranges start on row 2 we can this
version:

=IF(ROWS($1:1)<=G$1,INDEX(TN,SMALL(IF(MONTH(date)= 5,IF(MATCH(MONTH(date)&TN,MONTH(date)&TN,0)=ROW(TN )-1,ROW(TN)-1)),ROWS($1:1))),"")

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

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in
message
...

Recap
no blank cells in either coulmn
based on a cell address of J4 Need the results from a choosen month
to create a unique list of aircraft used in that month

SPB

"T. Valko" wrote:

Are there any empty/blank cells in either the date range or the
tail
number
range?

Well?

The answer to that question can either add complexity of eliminate
unneeded
complexity!

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote in
message
...
unique tail numbers by month only, The rest of the report used a
cell
ref
for
the date
the previous post did not work, thanks for any suggestions.
SPB

"T. Valko" wrote:

It looks like you want to extract unique tail numbers in a certain
date
range.

Are there any empty/blank cells in either the date range or the
tail
number
range?

The date range of interest, do you want just a specific month or do
you
want
the specific month of a specific year?

--
Biff
Microsoft Excel MVP


"Excel 2003 - SPB" wrote
in
message
...
Thanks in advance, I can not get my head around the sumproduct

Database seperate sheet "DATA"
C column =date formated as mm/dd/yy
F column = Aircraft No ei, N123AB

I am trying to product a unique list of aircraft used for the
month

I have the following but need to add the date element

=IF(SUMPRODUCT((Data!$F$2:Data!$F$3000<"")*1/COUNTIF(Data!$F$2:Data!$F$3000,Data!$F$2:Data!$F$3 000&""))<=COUNTA($V$129:$V129)-1,"",INDEX(Data!$F$2:Data!$F$3000,MATCH(TRUE,ISERR OR(MATCH(Data!$F$2:Data!$F$3000,$V$129:$V129,0)),0 ),1))

The V129 is where I want to start the list I estimate I will need
place
ment
for about 10 aircraft max