Conditional Summing across ranges using arrays

Hi all -

I'm struggling to find a formula that will solve my problem:

I have a matrix of numbers defined by a series of numerical column and
row headers.

RefCel  1     2    3     4      5 . . . m

1       a     #    #     #      #
2       #     #    #     #      #
..
5       b     #    #     #      #
..
n       #     #    #     #      #


What I'm trying to do is for a give column header and a subset of row
headers,
sum the intersecting values. Let's assume that the subset are held in
a named range Subset and the row and column headers are in range
called RowHead and ColHead and that the column I'm interested in is
ThisCol

I tried something like this
={sum(offset(RefCel,match(Subset,Rowhead,0),match(ThisCol,ColHead,0),1,1))}

So if ThisCol = 1, RowHead started at 1 and went to n, ColHead went
from 1 to m,
and Subset included a range with two values, 1 and 5, what I'm trying
to get to is a+b. Unfortunately, all I'm getting is a.

A complicating factor is that some of the values in Subset may not be
found in Rowhead.

Any thoughts on how I get this one done?

Thanks,

Marston
0
9/24/2003 5:36:38 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
379 Views

Similar Articles

[PageSpeed] 33

Reply:

Similar Artilces:

Reference Row across Worksheets
Hello, I am trying to reference a row from one worksheet to another. Everthing I have tried only display's the first column of the row. It does not have to be the complete row it can be just a group of cells in a row. Any help would be greatly apprecaited. Thanks dblwizard Nevermind. I finally figured it out. You have to use the "Paste Link" option. dblwizard ...

How can I use Avery products with Publisher?
Trying to print some business cards I've designed in Publisher using Avery 5371 Business Cards - but can't seem to set the print settings appropriately. I'm sure there is a magic button I'm missing? thanks! What problems are you having? We've seen so many different issues and I'd rather not have to dust off my crystal ball. ;-) -- JoAnn Paules MVP Microsoft [Publisher] "bakubug" <bakubug@discussions.microsoft.com> wrote in message news:C6BEF687-C74C-4AF6-95F6-E49252E72C62@microsoft.com... > Trying to print some business cards I've des...

Access Database; Use for clients
I need sorten amount of space for my college and business in access how do work this in Office Proffesional 2007? Douglas >...sorten sorten amount of space ... sorten is not a word as far as I know. certain amount of space? sorting amount of space? > how do work this in Office Proffesional 2007? work what? what exactly do you want to do? Regardless of what you mean by sorten, it is also unclear what you are asking. -- HTH, George "Douglas" <Douglas@discussions.microsoft.com> wrote in message news:0FCB82BE-4FF1-4FC3-98A7-1FC350C62694@microsoft.com... >I need...

Shutdown Outlook 2003 using Task Scheduler
Is this possible with XPH? I have recently loaded Shutdown Addin v1.8 which helps Outlook exit, or forces it down if necessary, when Outlook is manually shutdown. Unfortunately Shutdown Addin does not have scheduling capabilities - at least not that I know of. Thanx in advance for any input. SteveN Steve You can do with VB, VB Script etc. In VB it looks like this: Private Sub Form_Load() Command$ Dim explor As Object Dim olapp As Object Set olapp = GetObject("", "outlook.application") If Not (olapp Is Nothing) Then If (Command$ = "minimi...

dynamic sort macro across 3 linked sheets
Good evening -B3 thru M3 in sheets Row 3 in "Sheet1, Sheet1 (2), Sheet1 (3 contains symbols which are dynamic i.e. alphabetic and change in an one sheet or all sheets daily so that the alphabetic order is los almost daily but which order is critical to maintain order and sens when viewed. -what macro be devised to simply sort these changes across the thre linked sheets and keep good order- an array or 3-d referrence? - I giv up and so this request for help thanks -wrpalme -- wrpalme ----------------------------------------------------------------------- wrpalmer's Profile: http://...

Generate random numbers
Im looking for an addin og a tool in Excel to generate numbers using initial seed. Are there any tool available? Helge Helge - If you want to write VBA code, you could use RANDOMIZE and RND. If you want to use an add-in, there are many, and my RiskSim is one example, available at http://www.treeplan.com/risksim.htm - Mike http://www.MikeMiddleton.com "Helge's" <helge.klungland@plena.no> wrote in message news:25a7f68e-b3ce-4c5a-b11e-718ce5f31022@e7g2000yqf.googlegroups.com... > Im looking for an addin og a tool in Excel to generate numbers usin...

Named-range source-data for pie charts on copied worksheets
I have to finally concede that I'm stuck and have to ask for advice. I am using Excel 2000 and have been able to use Andy Pope's help on "Automatic removal of zero values in pie chart". That works fine. But if I copy the worksheet, the pie chart on the copied (new) worksheet references the named range as defined on the original worksheet. My named ranges, which the pie chart references, are: PieLabels=OFFSET(blank!$O$26,0,0,COUNT(blank!$N$26:$N$34),1) PieValues=OFFSET(blank!$N$26,0,0,COUNT(blank!$N$26:$N$34),1) So, one solution might be to define the same named range on dif...

How can I make a dinner seating arrangement using Excel?
I would like to make a seating arrangement for three round tables and 22 people using Excel. Is this possible? ...

Conditional formatting date
Xl 2000 user. We have a vacation template with the day of the month listed as (1,2,3,etc) starting in A6. The months are column headings starting in row 4 and are date formats listed as 12/1/03,1/1/04, etc. I set up conditional formats to change the patterns for weekend days. Those are : =WEEKDAY(DATE(YEAR(AK$4),MONTH(AK$4),$A36))=1 =WEEKDAY(DATE(YEAR(AK$4),MONTH(AK$4),$A36))=7 I tried to set up a third condition that will gray the incorrect days (i.e. November 31st) =MONTH(DATE(YEAR(AK$4),MONTH(AK$4),$A36))>MONTH(AK$4) This works for November, but does not work for any month with ...

Post disappeared -- "paid using the address information"
I posted this last week and I don't see my post or any replies. What exactly does Money mean when it says "this payee is paid using the address information"? Does it mean that MSN Bill Pay prints and mails a check to the payee? When I e-pay my Comcast bill, Money 2003 gives it a date that's two days from now. My other e-pays initially get today's date in the register (until the check clears, when it changes to the check cleared date). I wonder why Comcast gets a different date than other e-pay'ees. The Comcast bill has sometimes cleared two days after I e-...

I want access to ask for a number then use that number to print la
I have a table with all of the parts nessicary to build a component. I want the operator to enter the work order number and quantity required. I want Access to print out labels which contain all of the parts in my table and print the work order number on each label as well as multiply the quantity the operator entered by the quantity of each part in the table. So you want a user to enter a Work Order #, Component, and Quantity, and have the label print out part numbers and quantities? You can build a form with three text boxes on it and have the user fill out WO#, qty and Component. Y...

auto reply using "have server reply" option... problem
Hi, I am in the middle of migrating to Exchange 2003 and I have several accounts that needs to auto-reply when they receive a message. I have used the 'have server reply' option but it seems like it only works to emails sent inside the Exchange organisation. How do I make it reply to people outside my Exchange Organisation? Thanks, Christian Hi Check if Exchange is preventing automatic replies to external recipients.Open Inernet Message Formats and verify the allow automatic replies setting. /Daniel "Christian Jensen" <ads@peugeot205.dk> wrote in message news:44...

User-inserted external file path used to pull cell values
I have an organization that wants each vendor to fill out and submit a net present value worksheet as part of the RFP process. Call it the NPV workbook. The organization wants to then pull data from each of those worksheets into a summary worksheet. I would like for the organization to be able to input the file path for each NPV workbook. Then the summary cells would use the file path to pull the data. What I have been trying so far isn't working: 1) Organization creates a hyperlink to each NPV workbook; example: Cell B8 contains the hyperlinked string: F:\Emergent\Projects\03109 GSA ...

How to use WHERE and HAVING together?
Hi, Im having difficulty in using the HAVING statement with more than 1 criteria and would like to know how I might find the following? I have a table (fullres2) with horse racing details over numerous columns but need to find out.. How many of the horses that have the greatest % price interest go onto win. I can find out which horse has the greatest % move with the following..... SELECT eventref, MAX(overallmove) AS ["Biggest Shortner"] FROM fullres2 GROUP BY eventref HAVING MAX(overallmove) > 0; this uses eventref (race info which is the same over the number o...

The process cannot access the file because it is being used by another
Hi i'm getting this error when trying to browse through folder at any of the office applications "The process cannot access the file because it is being used by another process" however i do the same from notepad for example and it works fine please help thanks Hi Jacobo, your Outlook version is? -- Oliver Vukovics Share Outlook without Exchange: Public ShareFolder Share your contacts, calendars or e-mails http://www.publicshareware.com "Jacobo" <jacven@gmail.com> schrieb im Newsbeitrag news:1141757044.167158.235250@v46g2000cwv.googlegroups.com......

Code to look at range and extract data.
If the column of data has the text "Back On" I want to copy the adjacent columns to another sheet for each instance. This will be a loop since this text occurs more than once. If I understand you correct ? Start here http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JDJacobs" <JDJacobs@discussions.microsoft.com> wrote in message news:5B3B0D6A-CE19-426B-BBB6-F68B9AD6C2DB@microsoft.com... > If the column of data has the text "Back On" I want to copy the adjacent > columns to a...

Counting time ranges
I have a spreadsheet that looks like this... 07:51:4 07:56:2 07:59:5 08:36:0 08:36:2 09:04:2 09:08:1 09:12:2 09:13:3 09:15:4 09:17:3 09:17:3 09:20:1 09:23:1 09:24:1 I need to be able to count how many times each hour shows up, I tried using count if but the range isnt working? Please help!!! Cathy Hi maybe something like =SUMPRODUCT(--(A1:a100>=9/24),--(A1:A100<10/24)) to count the numer of 9 hour occurences >-----Original Message----- >I have a spreadsheet that looks like this.... >07:51:40 >07:56:27 >07:59:59 >08:36:01 >08:36:24 >09:04:25 >09:08:18 >09:...

Use this important update for Windows
--oygrkjphbs Content-Type: multipart/related; boundary="fsveuaegtt"; type="multipart/alternative" --fsveuaegtt Content-Type: multipart/alternative; boundary="esrvjidpsty" --esrvjidpsty Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to protect y...

Ok to use mount points on a single-server E2K3 with DAS?
Hello, Just wondering if there are any negative side-effects to using mount points instead of drive letters on a single server with direct-attached storage running E2K3? So we'd create C:\TransactionLogs pointing to a mirrored pair, C:\Database pointing to a 3-disk RAID5 array and use C: itself for the OS and Exchange binaries. We'll never run out of drive letters but mount points do look clearer. Thanks, - Alan. Mount points are fully supported, just be sure your logs and database are on separate physical spindles for recovery/performance. Todd "Alan" <bruguy@gm...

how do I get data from an array into one cell (comma delimited)
looking for a way to query an array 3 cells by 3 cells (they have numeric data only) and return a value in a seperate cell that has all the numbers in the array (comma delimited) thanks If C1 thru E3 contain: 1 2 3 4 5 6 7 8 9 then: =C1 & "," & D1 &","& E1 &","& C2 &","& D2 &","& E2 &","& C3 &","& D3 &","& E3 will return: 1,2,3,4,5,6,7,8,9 -- Gary's Student "bb" wrote: > looking for a way to query an array 3 cells by 3 cell...

conditional format multiple colours #2
not sure i understand about testing for a string? I undersand about using condtional fomat for 4 values , but I have possiblty of 8 Joh -- atme ----------------------------------------------------------------------- atmel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1615 View this thread: http://www.excelforum.com/showthread.php?threadid=27586 John, I mean that I am reading that the cell will contain say '0800 - 15:00', not a time value, so you test for a string not a number. If you have 8 it is probably best to use worksheet events. Here is an ...

Icon on WindowList menu using MDIChild Form icon.
hi, I would like to know is there any way on how to put icon on WindowList type menu using MDIChild's own form icon in VB6? Regards, Alex ...

Excel 2002 copying and pasting some time hangs using key strokes
It happens sometimes: In excel 2002,the paste button will be grayed out if you use key strokes, not mouse to copy and paste. Only way to go to resolve is to Crtl-alt-del. Is any one has resolution on this. Or information why this happens. Dr Tim ...

can I use a template from publisher for a powerpoint presentation
I want to use the Layers template in Publisher for a Powerpoint presentation so that the look of my company stays consistent. Any suggestions how to do this? You can copy/paste the objects. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "KHiemstra" <KHiemstra@discussions.microsoft.com> wrote in message news:20565D6D-4608-4E55-8251-593DE51CB7D0@microsoft.com... >I want to use the Layers template in Publisher for a Powerpoint presentation > so that the look of my company stays consistent. Any suggestions how to ...

Conditional Formatting and Borders
I'm trying to add thick borderlines on my spreadsheet using conditional formatting. But the "thick" option is not available. I observed the code using the macro recorder which produced Sub ConditionalFormat1() Range("A5:O428").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A5<>$A6" With Selection.FormatConditions(1).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 3 End With End Sub .... I thought I could chagne the ...