New to SUMPRODUCT .. is there a better way?

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

(clare reads his mail with moe, nomail feeds the bit bucket :-)


0
8/23/2011 12:49:42 AM
excel 39879 articles. 2 followers. Follow

1 Replies
378 Views

Similar Articles

[PageSpeed] 46

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.



"Clif McIrvin" <clare.nomail@gmail.com> wrote in message 
news:j2utf7$es5$1@dont-email.me...
> 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.

> 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
>
> (clare reads his mail with moe, nomail feeds the bit bucket :-)
>
>



-- 
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


0
8/23/2011 12:59:33 AM
Reply:

Similar Artilces:

Is there a way to use "Custom Login Page"?
I know only users from Active Directory can use CRM System. But is there a way to let users login on Web form instead of windows prompt? And how can we integrate it with existing system? Thanks This may help you: You can make Internet Explorer prompt for user names and passwords by changing the security settings. Start Internet Explorer, then select Tools | Internet Options. Click the security tab and click on Local Intranet (depending on how your network is managed you may need to choose Trusted Sites instead). Then click on Custom Level. In the Security Settings dialog box scroll to th...

test post form new computer
Woohoo! Paul B wrote: -- Dave Peterson ec35720@msn.com ...

ecommerce in the new GP8 gone the way of the dinosaur?
So has the GP ecommerce module gone the way of the dinosaur in 8.0? If so what's taking it's place for companies that need a sophisticated online presence to sell their GP inventory? econnect? We've used GP ecommerce from 5.5 through 7.5 on one of our sites, and though we're not even close to interested in GP8 yet, I'd like to start having on my mind what we'll need to do when the time comes. Thanks, Rob Wuhrman ...

First Parameter in INDIRECT command changes if I add new column
When I use the INDIRECT command, as in the following example, PERCENTILE(INDIRECT("$FM"&$Y140):$FM140, FN$1) it works fine until I insert a new column anywhere before the column containing the IDIRECT command. If I do that, the first parameter in the command changes, PERCENTILE(INDIRECT("$FL"&$Y140):$FM140, FN$1) notwithstanding the $ that I had put there to prevent its changing. Is there any way to avoid that? -- Delaware Mac Try putting the FM into a cell also to be used as part of your indirect. Let's say you put ...

New on-line services at institution but Money 2004 doesn't show th
Fairly recently my credit card issuer added online account download services. I have gone to the institution and used it to download a qif file. But when I click on this institutions name in the list presented by on-line services manager it doesn't show any services. (The name is in the list.) Is there a process where the list is updated with newly offered services? If so, how long should it take for a new one to appear. Or is there someone I can notify with the name of the institution who can then add the service. Thanks -- Harris S. Freeman Alliance Strategies and Programs ...

My new book is available
http://www.iil.com/iil_shortdescr.asp?sku=PT248 Congratulations, Bob! ************ Anne Troy www.OfficeArticles.com "Bob Umlas" <Excel_Trickster@msn.com> wrote in message news:uS5sYwJtFHA.664@tk2msftngp13.phx.gbl... > http://www.iil.com/iil_shortdescr.asp?sku=PT248 > > Bob Umlas wrote... >http://www.iil.com/iil_shortdescr.asp?sku=PT248 So unsollicited commerical postings are OK when it's MPVs who are sending them? I'm NOT an MPV! MVP, yes, but not MPV! Hey! I'm excited! Excuuuuse me! :-) "Harlan Grove" <hrlngrv@aol.com> wrote...

NETDDE two way communication
Friends, I am using Excel 97(Ver.8) in Windows NT4. I was successful in communicating between two excel sheets using NETDDE, configuring one PC as Server and other as client. But the communication was only possible in one direction. It will be really nice, if you could tell me how to communicate two way between two excel sheets in different PCs connected through a LAN network. and also how to configure DDESHARE for this particular purpose. Regards Hareesh Hi Hareesh, I don't think you can do that with NETDDE, and the only ways I can think of doing it require programming. The si...

new #2
http://clixncash.com/ptp2.php?p=r&ref=price ...

Pressing "new email" causes "program addressing address book" warning
I am using Outlook XP on Windows 2000. My system is virus free. If I want to write a new email, I press the "new email" button in Outlook, and I get that warning message "A program is trying to access your address book - do you wish to continue, Yes/No". I have to tick the ""allow for one minute", then "Yes" before I can write an email. Can anybody please tell me how to fix this annoyance. Ric <spam@off.com> wrote: > I am using Outlook XP on Windows 2000. My system is virus free. > > If I want to write a new email, I press the &...

admin permissions on existing and new users
hello, unfortunately we have a security problem we don´t know how to fix at the moment. we noticed on our existing users and on new ones that a few accounts have more rights than it was a few weeks/months ago. when we open the properties of a user, and select security the following accounts have full control or nearly full control - including "Send As": Account Operators, Administrators, Domain Admins, Enterprise Admins, and a few own delegation groups. additional the group that should have full control including send as is no longer listed on the users security tab! ...

Outlook launches a new Instance of Explorer by clicking each hyperlink
How can I get Outlook 2003 to reuse the existing instance of Explorer instead of launching a new window each time? ...

New Installation No Z report 0.00
New Installation No Z report 0.00 This is a new installation and the Z report has no detail. 0.00 all the way across. Any ideas? -- precisecheck hi dear, Check the database> registers> receipt format is there and also check database> register has attached any receipt format. Make sure these things first as it not setup by default. "PreciseCheck" wrote: > New Installation No Z report 0.00 > > This is a new installation and the Z report has no detail. 0.00 all the way > across. Any ideas? > -- > precisecheck Thank You Akber, this resolved the pr...

Delete a record and save a new record from form
I have a form "frmDuplicateValues" that pops up if certain criteria are met on "frmStopDailyCount". If they choose to delete a record, I want it to automatically go back to frmStopDailyCount and act as if they clicked a button on that form. That is, if they click cmdDelete on frmDuplicateValues (which will delete a record then close that form), can it somehow go to cmdAddStats on frmStopDailyCount and activate it? Thanks. ...

new / delete in a dll
Hi, I have a dll that allocs some memory with 'new' and returns the pointer to the memory: in dll: char* p = new char[100]; return p; My main application then calls delete on the pointer directly (not via a call to the dll), and this causes a fault. the dll is still loaded, i have not free'd it at this point. It appears that i cannot delete memory that is allocated in the dll. Is this correct? And if so, why?! JoeB wrote: > Hi, > > > I have a dll that allocs some memory with 'new' and returns the pointer > to the memory: > > i...

Replicating a Chart with new data
I have formatted a chart using a specific data set referring to one enterprise. I want to duplicate / replicate that same chart for a second and third etc enterprise from different data on the same worksheet. I want to have a series of separate cgarts within the workbook all dynamically linked to the data sheet. I hope this is clear - I amtrying to save time by not having to reformat every new chart as there are 47 of them! Hi Patrick, You could copy the chart, then select the data series and change the range that is referred to. Or you could build a chart that refers to a range in th...

Is there a quick way to page breaks through out 25,000 line sheet
I need to place bage breaks in specific places on a sheet that has 25,000 lines. Is there an easy way to do this? 1) Type all row numbers where you want to declare Page-Break. 2) Copy/Paste the following macro to the VB Editor. 3) Select the range of cells and run that Macro. --------------------------------------- Sub AddPageBreaks() For Each CL In Selection Cells(CL + 1, 1).Select ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell Next [A1].select End Sub ------------------- Micky "Stacy" wrote: > I need to place b...

outlook 2003 freezes when clicking on the paper clip to add an attachment when creating a new mail
Report says there is a winword error so I unchecked Word 2003 as mail editor and outlook works fine. Is this bug well known? Best regards, XL What service pack level is Office System 2003 at? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, Zazou asked: | Report says there is a winword error so I unchecked Word 2003 as mail | editor and outlook works fine. | | Is this bug well known? | | Be...

Disable grouping feature when making new folders in Outlook 2003q?
Hi there, Is there a way to disable the grouping feature when making new e-mail folders? I know I can manually disable for every existing folders. However with making new folders, I have to manually ungroup. I don't like the days and dates grouping feature. I am old school/fashion so... Thank you in advance. :) -- Phillip Pi Senior Software Quality Assurance Analyst ISP/Symantec Online Services and Norton SystemWorks (PC) Symantec Corporation www.symantec.com ----------------------------------------------------- Email: phillip_pi@symantec.comSYMC (remove SYMC to reply by e-mail) ---...

Bank Merger so will lose Direct Money services so looking for new bank
The new bank tells me that driect services in MS Money are insecure and = too costly. I had rejected having an account at their bank because of = its lack of support for direct services and billpay within Money. So = now I am back to looking for a new bank. I prefer a national bank with = good money market rates. I don't mean 1 or 2%, which works both ways = with MS Money. Anyone have a suggestion. By the way, I don't think Money transactions are insecure. I have been = using them for 10 years without incident. What do you think? "SteveC" <sconklan.nospam.yahoo.co...

Deliver new mail to the following location (none)
How do I change the (none) to something Outlook will recognize so that Outlook will statrt What version of Outlook? Do you have a mail account configured? Do you have an Outlook Data file configured? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Philip asked: | How do I change the (none) to something Outlook will | recognize so that Outlook will statrt ...

Can't use new banks previously not in 2004 for auto downloads
I use National City and they are now listed in the banks for online services. I try to setup online services for my account and when I select them out of the list it starts downloading something but ultimately says account setup or online setup is unavailable and to try again later. Is this the Yodlee problem people keep speaking about? ...

Problem following new install of OutlookXP
I am using Outlook XP and have just reinstalled it from scratch since I have just done a new install of Windows XP Pro SP1. I have multiple users on my system and when everyone else logs in they can use the File>Import/Export wizard to transfer POP email account settings. (I have found this the easiest way to move email account settings by saving IAF files in Outlook Express - tedious but it works). However, on my own account I get an error when I select "Import Internet Email Account Settings" which says "No internet accounts were found to import". This is strange bec...

EASY WAY TO GET MONEY
You will earn money from first minute. You will not be rich but how about 500$ in a week. Try this adress and invest 30$. http://www.waybucks.com/promo.php?from=roardos ...

Provide a way to remove menu options if user doesn't have access
If a user does not have access to the Sales module, for example, provide the ability remove all menu choices that relate to that module. So, they would not see "Sales" under Transactions, Inquiry, Reports, etc. The customer that I am working with would like to remove the System menu choice for a group of users. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the su...

New Style Worksheet to upload static item attributes
With reference to my case at below link: https://mbs.microsoft.com/support/UpdateIncident.aspx?CaseNumber=9080792 Some of the Store static data created during day to day store operation need to update from Store to HQ. For example, when store receive goods and enter weight information, Aliase info from supplier and etc. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the sugg...