# sumproduct to create a unique list

G

#### Guest

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\$2ata!\$F\$3000<>"")*1/COUNTIF(Data!\$F\$2ata!\$F\$3000,Data!\$F\$2ata!\$F\$3000&""))<=COUNTA(\$V\$129:\$V129)-1,"",INDEX(Data!\$F\$2ata!\$F\$3000,MATCH(TRUE,ISERROR(MATCH(Data!\$F\$2ata!\$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

G

#### Guest

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

T

#### T. Valko

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?

G

#### Guest

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

#### T. Valko

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

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

G

#### Guest

no blank cells in either columns
spb

T. Valko said:
Well?

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

G

#### Guest

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

#### T. Valko

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)

G

#### Guest

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
Thanks again

SPB

T

#### T. Valko

You're welcome. Thanks for the feedback!