Conditional sum - Two dimensional (advanced)

Hi 

Can you help? 

I would like to sum a matrix according to a condition in a row and in 
different condition in a column. I know how to do this one dimensionally 
(either in the row or the column) using sumproduct, but what about two 
dimensions?

Let me give an example, number of widgets produced each week by each type of 
machine

     A           B          C          D
1   weeks>>1           2          3 
2   Type 1   10         15         11 	
3   Type 2   20         5           10  
4   Type 1   5           12         21
Etc


So, I’d like the formula to be able to Sum the number of widgets produced by 
machine type 1 after week1 (ie. Weeks >1)

The formula should give the answer 15+11+12+21 = 59

Thank you 
   

p.s. No macros please

0
Utf
2/18/2010 12:35:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
600 Views

Similar Articles

[PageSpeed] 47

Hi,

I reproduced your table in A1 to D4

and then the formula. Note carefull each of the ranges when setting this up 
for your table

B1:D1>1 Is the header row excluding A1
A2:A4="Type 1" is the header column excluding A1
B2:D4 Is the data range excluding the headers

=SUMPRODUCT((B1:D1>1)*(A2:A4="Type 1")*B2:D4)


-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"StephenT" wrote:

> Hi 
> 
> Can you help? 
> 
> I would like to sum a matrix according to a condition in a row and in 
> different condition in a column. I know how to do this one dimensionally 
> (either in the row or the column) using sumproduct, but what about two 
> dimensions?
> 
> Let me give an example, number of widgets produced each week by each type of 
> machine
> 
>      A           B          C          D
> 1   weeks>>1           2          3 
> 2   Type 1   10         15         11 	
> 3   Type 2   20         5           10  
> 4   Type 1   5           12         21
> Etc
> 
> 
> So, I’d like the formula to be able to Sum the number of widgets produced by 
> machine type 1 after week1 (ie. Weeks >1)
> 
> The formula should give the answer 15+11+12+21 = 59
> 
> Thank you 
>    
> 
> p.s. No macros please
> 
0
Utf
2/18/2010 12:47:01 PM
Works a treat. Wow, Sumproduct works across a matrix. 

Is it wrong to love a formula? If so, I don't care, I ♥ SUMPRODUCT

Thanks Mike

"Mike H" wrote:

> Hi,
> 
> I reproduced your table in A1 to D4
> 
> and then the formula. Note carefull each of the ranges when setting this up 
> for your table
> 
> B1:D1>1 Is the header row excluding A1
> A2:A4="Type 1" is the header column excluding A1
> B2:D4 Is the data range excluding the headers
> 
> =SUMPRODUCT((B1:D1>1)*(A2:A4="Type 1")*B2:D4)
> 
> 
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "StephenT" wrote:
> 
> > Hi 
> > 
> > Can you help? 
> > 
> > I would like to sum a matrix according to a condition in a row and in 
> > different condition in a column. I know how to do this one dimensionally 
> > (either in the row or the column) using sumproduct, but what about two 
> > dimensions?
> > 
> > Let me give an example, number of widgets produced each week by each type of 
> > machine
> > 
> >      A           B          C          D
> > 1   weeks>>1           2          3 
> > 2   Type 1   10         15         11 	
> > 3   Type 2   20         5           10  
> > 4   Type 1   5           12         21
> > Etc
> > 
> > 
> > So, I’d like the formula to be able to Sum the number of widgets produced by 
> > machine type 1 after week1 (ie. Weeks >1)
> > 
> > The formula should give the answer 15+11+12+21 = 59
> > 
> > Thank you 
> >    
> > 
> > p.s. No macros please
> > 
0
Utf
2/18/2010 1:49:02 PM
Gald I could help,

I'm inviting contrary opinion but I believe Sumproduct to be the most 
powerful and versaltile formula in Excel
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"StephenT" wrote:

> Works a treat. Wow, Sumproduct works across a matrix. 
> 
> Is it wrong to love a formula? If so, I don't care, I ♥ SUMPRODUCT
> 
> Thanks Mike
> 
> "Mike H" wrote:
> 
> > Hi,
> > 
> > I reproduced your table in A1 to D4
> > 
> > and then the formula. Note carefull each of the ranges when setting this up 
> > for your table
> > 
> > B1:D1>1 Is the header row excluding A1
> > A2:A4="Type 1" is the header column excluding A1
> > B2:D4 Is the data range excluding the headers
> > 
> > =SUMPRODUCT((B1:D1>1)*(A2:A4="Type 1")*B2:D4)
> > 
> > 
> > -- 
> > Mike
> > 
> > When competing hypotheses are otherwise equal, adopt the hypothesis that 
> > introduces the fewest assumptions while still sufficiently answering the 
> > question.
> > 
> > 
> > "StephenT" wrote:
> > 
> > > Hi 
> > > 
> > > Can you help? 
> > > 
> > > I would like to sum a matrix according to a condition in a row and in 
> > > different condition in a column. I know how to do this one dimensionally 
> > > (either in the row or the column) using sumproduct, but what about two 
> > > dimensions?
> > > 
> > > Let me give an example, number of widgets produced each week by each type of 
> > > machine
> > > 
> > >      A           B          C          D
> > > 1   weeks>>1           2          3 
> > > 2   Type 1   10         15         11 	
> > > 3   Type 2   20         5           10  
> > > 4   Type 1   5           12         21
> > > Etc
> > > 
> > > 
> > > So, I’d like the formula to be able to Sum the number of widgets produced by 
> > > machine type 1 after week1 (ie. Weeks >1)
> > > 
> > > The formula should give the answer 15+11+12+21 = 59
> > > 
> > > Thank you 
> > >    
> > > 
> > > p.s. No macros please
> > > 
0
Utf
2/18/2010 2:15:01 PM
Reply:

Similar Artilces:

Advanced find issue OR
When using advanced find, there is a limitation with the OR clause. Ex: Want to find ALL active contacts that have a certain str field populated OR is on a certain Marketing list. This is not possible. It should be possible to mark the 2 lines/groups and click the OR group. ---------------- 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 suggestion in the Microsoft Web-based Newsreader an...

Re: Keeping a SUM filed clear Until All Numbers Entered
Thanks for the helpful replies. Carl -- If you can read this, thank a teacher.... If you are reading it in English, thank a Veteran..... ...

scatter plot with two colors
Hi, IS it possible to have a scatterplot with one series but two different colors ( apply two different colors to the data points)? -- Best regards, Edward If you want to change the colour of an individual data point you can use "Format Data Point" rather than "Format Data Series", but if you wish to do this for a range of points it makes more sense to define them as a separate series. -- David Biddulph "Edward" <Edward@discussions.microsoft.com> wrote in message news:B9DC24C4-3CA3-46B5-8713-0B19659AB402@microsoft.com... > Hi, > IS it possible ...

I need to create a two part formula
The formula below will return a GPA for our salespeople =IF(H7>0.9,4,IF(H7>=0.8,3,IF(H7>=0.7,2,IF(H7>=0.6,1,0)))), but I need to add one more criteria and that if another cell (C4) = 0 then the results of this formula need to be 0 as well. I hope this makes sense, can someone please help me. Thanks! Something like: =IF(C4=0,0,H7>0.9,4,IF(H7>=0.8,3,IF(H7>=0.7,2,IF(H7>=0.6,1,0))))) Regards, Fred "DMW" <DMW@discussions.microsoft.com> wrote in message news:1F81AAD9-C057-4504-8B01-8C1786BF0DD4@microsoft.com... > The formula below wi...

One "Resume Next" or two?
Suppose I have error handling code similar to the following: Err_MyFunction: on error resume next rst1.close rst2.close I don't want to see an error message from trying to close recordset vars which are already closed. I actually want/expect them to be closed. But of course, depending on where the error occurred, one or both rst vars might still be open when the error handler fires, so I put the "resume next" statement to guard against this eventuality. But suppose both rst vars are already closed when the error handler fires? In this case, both close state...

Do a two way lookup and get the result in multiple columns
Hi, I have a worksheet with data on store sales: StoreLoc Date Sales$ A 10/28/2005 $100 A 11/04/2005 $150 B 10/28/2005 $200 B 11/04/2005 $250 B 11/11/2005 $300 B 11/18/2005 $350 C 11/04/2005 $300 C 11/11/2005 $350 C 11/18/2005 $400 D 11/11/2005 $400 etc. I wanted to do a two-way lookup on StoreLoc and Date and get the sal...

Arcserve 11.5 failed to backup two mailboxes out of 103
We use ArcServe 11.5 to do a document level backup of our Exchange 2003 mailboxes. When the application ran for the first time last night on the production server, after testing on a test server, the notification I recieved was that the backup job was incomplete. It failed to backup two mailboxes out of 103. How can I determine which mailboxes were not backed up and more importantly, why they were not backed up. I am not sure if this is an Exchange 2003 issue or an ArcServe issue. My inclination is that it is an Exchange 2003 issue because it did successfully back up the other 1...

Array Formula isn't summing but counting
I need to sum information based on several other criteria -- accoun number, sort codes, company names. The array formul {=SUM(A1:F25="A")*(E1:E25>0))} returns a count rather than a sum Does anyone know of a way get it to add my data rather than countin how many? Staci -- Message posted from http://www.ExcelForum.com Hi if you want to sum column E try the array formula =SUM((A1:F25="A")*(E1:E25>0)*(E1:E25)) or a non array formula =SUMPRODUCT((A1:F25="A")*(E1:E25>0),E1:E25) -- Regards Frank Kabel Frankfurt, Germany > I need to sum information ...

Conditional vlookup
I have a named Range "Price" Ihave the folowing formula that works fine =IF(B3>0,VLOOKUP(B3,Price,3,FALSE),"") Except in column A I have a Manufactures name. Column B contains the part number. I want to only use vlookup on range "price" for matches to column a What is the best way to handle this? Thanks Saved from a previous post: If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100),0)) (all in one ...

Conditional Formatting in Forms
I'm trying to get a form to display a "Yes" or "No" depending on a review of two dates. Tried a number of times. In English, want it to do the following - if the Date returned to marketing is less than or equal to the due date, write "Yes" and if not write "No" - otherwise leave blank. ans = iif(dateReturned <= dueDate,"Yes", "No") >write "Yes" and if not write "No" - otherwise leave blank. What instances you want to leave it blank, since date returned equal or less than due date is a &qu...

Sum From Beginning of Column #2
How do I create a formula that adds from the top of a column? Here's the situation. I have a column of numbers (with a header of "Pay"). To the right of that colum I want to keep a running total ("Yearly Total"). Every time I get paid, I'm going to enter the amount in the pay column and then I want the Yearly Total column to automatically add it. When I create a fomula (say =SUM(B2:B21)), and propigate it down, it doesn't start at the top. In others words the next formula is =SUM(B3:B22). I want it to be =SUM(B2:B22). And, if a value is not filled in on t...

two colum lookup #2
i would like to see the cells in colum A & B which coiside with the cells in colums F & G i will ammend the original so that i can be understood better and send shortly -- alexanderd ------------------------------------------------------------------------ alexanderd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4984 View this thread: http://www.excelforum.com/showthread.php?threadid=278114 ...

Two Personal.xls Files
I have 2 personal.xls files in different directories. At startup one opens. Both personal.xls are identical. The problem I have is a custom toolbar that refers to the closed personal.xls file. I have many buttons on the toolbar. Is there a way I can change the file path to the referenced personal.xls to the open personal.xls file or do I have to change each button one at a time. TIA Greg Try this. Move the file that is loading automatically out of the XLSTART directory. Then click on one of the buttons so it loads your "closed" Personal.xls file. Once you've done that, unhide...

Conditional Format, Color Scale adjacent cells
I have a list of companies from which I receive periodic reports. Each company has a weight of importance In my list I record the incoming reports saying either "Missing" or "OK" I want to highlight all "Missing" with a colour scale that reflects the company's weight. For that I would create a hidden helper column (let's call it Code) with a formula like Code = Weight * if(Received = "Missing", 1, 0) I tried Conditional formatting / Color Scale but found that it applies only to the values of the cells containing the different value...

Formula for vlookup and then sum
Hello. I need a formula to do a vlookup for whenever a certain cost code/cost type appears in column A, then go to column F and sum the dollar amounts. Help please :) Hi, You can only use full columns in E2007 for E2003 and earlier define a shorter range =SUMPRODUCT((A:A="Your cost code")*(F:F)) Mike "Alberta Rose" wrote: > Hello. I need a formula to do a vlookup for whenever a certain cost > code/cost type appears in column A, then go to column F and sum the dollar > amounts. Help please :) 2003 use array formula { =SUM((A1:A10...

Two Exchange servers no connection between mailboxes
Hi, I have just installed an Exchange 2003 server in the same forest as an Exchange 2000 server. The idea is to transfer all the mailboxes across when i'm satisfied the new server is doing everything I need. I moved two mailboxes today and have struck an issue. These two mailboxes can send mail to one another, but neither can send or receive mail from mailboxes still on the original exchange server. Is this normal? I would prefer not to move all the other mailboxes until I know it's working but I can't test it properly with this restriction. Thanks, John ...

How do I select two colors for Gradient Fill Excel 2007
Hi, I get the one color, but it seems that the other color defaults to a color that I can't change. Please help. Thanks -- Hennie I dont have a solution but a suggestion...... I would record a macro doing what you want manually and then inspect the generated code to see what you are missing in your code. Chrisso On 14 Aug, 00:04, Hennie <Hen...@discussions.microsoft.com> wrote: > Hi, > > I get the one color, but it seems that the other color defaults to a color > that I can't change. > > Please help. > > Thanks > -- > Hennie ...

Two mailservers
Hi, I have two mailservers in the same domain. Both Exchange 2003 SP1. The first mail server (EXCHANGE1.DOMAIN.COM) is situated in New York. The second mail server (EXCHANGE2.DOMAIN.COM) is situated in London. Most of my mailboxes reside on EXCHANGE1 and the users have been using this for years. The new server, EXCHANGE2, has only a new account on it - a test account. When I OWA to this account then I can see my email fine - and I can send email out to external addresses without issue. However, if I try and send an email to ANYBODY@DOMAIN.COM then it just sits on the server - it see...

how do I create "blink" conditional formatting?
how do I create "blink" conditional formatting? Hi You can't, you need VBA for that. See this link: http://www.cpearson.com/excel/BlinkingText.aspx HTH John "OM PRAKASH" <OM PRAKASH@discussions.microsoft.com> wrote in message news:B83FD6E8-F264-41F8-B179-E5DBF6865872@microsoft.com... > how do I create "blink" conditional formatting? ...

Advanced month deposit should be shown in the statement after PTR
Posting for Kenny Wu Of Professional Advantage PTY LTD. Details: After running PTR on a given cut-off date (e.g. 8/31/2007). Transaction's Apply date field like 9/31/2007 should still be shown in the RM statement eventhough you have posted them on or before the PTR's cut-off date. ---------------- 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 suggestion in the Microsoft Web-...

Sum by month and year
I am trying to get daily totals sumed and put into a monthly worksheet. I have been using SUMIF comparing the month and year of each payment. Here is the formula. =SUMIF(MONTH(Payments!b7:b3000)&YEAR(Payments!b7:b3000), MONTH(B39)&YEAR(B39),Payments!F7:F3000) Excel is saying there is an error but when I look art the edit formula area is seems to calculating correctly. Can you help me? Steve You can use sumproduct without the CSE =sumproduct((month(dr)=month(b39))*year(dr)=year(b39))*sumrng) -- Don Guillett SalesAid Software donaldb@281.com "Steven Robilard" <stev...

How to combine two reports in one chart
I'm working on a shipping report that includes 3 different shipping regions. I created new reports for each region and now I want them to be in one common chart. How do I do that? Byron720;575293 Wrote: > I'm working on a shipping report that includes 3 different shipping > regions. > I created new reports for each region and now I want them to be in one > common > chart. How do I do that? create a table that consolidates your three reports and base the chart on that table. -- teylyn Telyn -- 'teylyn.posterous.com' (http://teylyn.pos...

Offset, sum down to the first blank row
can anyone write the formula to sum down to the first blank row it encounters? In the sample data below I want the xx to sum only to 60 Assume the word sum is in cell A1 and the xx is in cell B1. sum xx Blue 10 Black 20 Red 30 Red 20 black 40 thanks much. p.s. would this formula be considered "volatile" and therefore "risky"....if so, what's the risk. thx. "Tami" <Tami@discussions.microsoft.com> wrote in message news:EFA4F8A3-A823-4522-A946-F55AB55E0B29@microsoft.com... > can anyone write the formula to ...

Conditional Recordcount
Experts, I'm trying to get a record count based on the value in the combox. For example if 2 records are marked "completed" out of 5, I want 2 as my record count. Please help! thanks I don't understand. The RecordCount is a PROPERTY of a recordset, and is only available to you when you create and open a recordset. You cannot "set" its value to something. "Shiller" wrote: > Experts, > > I'm trying to get a record count based on the value in the combox. > For example if 2 records are marked "completed" out of 5, I want 2 a...

Checking on two values
To count the numbers of rows that include the number "16" in column "I", I use the following formula: ANTALL.HVIS(I:I;"16") (I think this is called COUNT.IF in english) I want to count the numbers of rows that include both this and the letter "A" in column "B". (Meaning not counting rows which just fulfill one of these requirements.) How do I write this formula? (Please feel free to write the english formula, I will find out how to write it in norwegian.) Regards Johannes Hei Johannes COUNTIF takes only one criteria. You can do this wit...