New to SUMPRODUCT .. is there a better way?

[Clif McIrvin]

Just starting to work with SUMPRODUCT, thanks to references to Debra
Dalgleish's blog -
http://blog.contextures.com/archives/2009/06/17/excel-for-underdogs/

and her link to Bob Phillip's write-up:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

in these NGs.

I have a table of historical data (laid out as a table, not actually
formatted as an xl2010 table nor xl2003 list) where each row represents
a single sample and it's associated data.

I'm working to create, on a different worksheet in the same workbook, a
summary showing a) the oldest and b) newest sample date and c) the
height of the newest sample.

Using a combination of SUMPRODUCT and { MAX ( IF () ) } I have my
summary lookups working -- except that if there happen to be two or more
samples on the newest date SUMPRODUCT returns the sum of the heights. I
understand why; I just have not discovered how to work around it.

Anyone willing to have a look-see and offer suggestions for a
work-around and/or a better approach?


Here's the worksheet formula that returns the height of the newest
sample:

RC4=INDIRECT(ADDRESS(SUMPRODUCT(--(OFFSET(cylData,0,1,,1)=RC3),
--(OFFSET(cylData,0,0,,1)=RC1),ROW(cylData)),3,,,"Sheet1"))

cylData is a defined name (range) on sheet "Data" (the range does not
include the column headers)
cylData=Sheet1!R2C1:R15C3 for the sample data below
RC3 is the result of my newest sample date lookup
RC1 is the job identifier (Location)

The RC3 formula:

{ =MAX(IF(OFFSET(cylData,0,0,,1)=RC1,OFFSET(cylData,0,1,,1),0) ) }

The formula to return the oldest sample date (using helper cell RC5):

RC2=INDIRECT(ADDRESS(ROW(OFFSET(cylData,RC5-1,0,1,1)),2,,,"Sheet1"))
RC5=MATCH(RC1,OFFSET(cylData,,1,,1),0)

And a simplified .csv sample of the data to illustrate the nature of the
data (in particular, I omitted many columns for brevity):

Location,Sample Date,Height
Glen Elder,8/1/2011,36'
Ellinwood'11,8/2/2011,72'
Holyrood'11,8/3/2011,roof
Ellinwood'11,8/3/2011,80'
Glen Elder,8/3/2011,44'
Ellinwood'11,8/4/2011,92'
Glen Elder,8/4/2011,56'
Ellinwood'11,8/5/2011,100'
Daykin,8/8/2011,Roof #1
Daykin,8/8/2011,Roof #2
"Wolf, KS",8/8/2011,8'
Ellinwood'11,8/8/2011,108'
Glen Elder,8/9/2011,72'
Glen Elder,8/11/2011,88'


The results from the above sample data in .csv format:

Location,First Sample,Last Sample,Height,Helper
Daykin,8/8/2011,8/8/2011,0,9
Ellinwood'11,8/2/2011,8/8/2011,108',2
Glen Elder,8/1/2011,8/11/2011,88',1
Holyrood'11,8/3/2011,8/3/2011,roof,3
"Wolf, KS",8/8/2011,8/8/2011,8',11

Note the incorrect height value for Daykin.

 

[Clif McIrvin]

Typo in original: SUMPRODUCT returns the sum of the heights
should read: SUMPRODUCT sums the rows of all samples on that date and
returns the height from that row number. Fortunately for me, that
normally results in a row beyond the data - resulting in an easy to spot
(zero) value.

Just starting to work with SUMPRODUCT, thanks to references to Debra
Dalgleish's blog -
http://blog.contextures.com/archives/2009/06/17/excel-for-underdogs/

and her link to Bob Phillip's write-up:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

in these NGs.

I have a table of historical data (laid out as a table, not actually
formatted as an xl2010 table nor xl2003 list) where each row
represents a single sample and it's associated data.

I'm working to create, on a different worksheet in the same workbook,
a summary showing a) the oldest and b) newest sample date and c) the
height of the newest sample.

Using a combination of SUMPRODUCT and { MAX ( IF () ) } I have my
summary lookups working -- except that if there happen to be two or
more samples on the newest date SUMPRODUCT returns the sum of the
heights.

SUMPRODUCT sums the rows of all samples on that date and returns the
height from that row number. Fortunately for me, that normally results
in a row beyond the data - resulting in an easy to spot (zero) value.