Sumproduct or ?????

Hi All,

I have 2 tables one is cust table and the other one is amount table.

A	B		C	D
Customer Table		   Amount Table	
Parent ID	Cust #		Cust #	Amount
2001	AA		AA	5
2001	BB		AA	5
2001	CC		AA	5
2001	DD		AA	5
2001	EE		AA	-5
2001	FF		AA	-5
2001	GG		BB	3
			BB	3
			BB	-3
				
Summary				
				
AA - I want to be able to count if "AA" in Cust table (Column B) then count 
positive amount minus negative amount in amount table (Column D). In this 
case the answer is "2"				

BB - The same thing with "BB". The answer is "1"				
Thank you
John

0
Utf
11/19/2009 7:30:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
1088 Views

Similar Articles

[PageSpeed] 15

John wrote:
> Hi All,
> 
> I have 2 tables one is cust table and the other one is amount table.
> 
> A	B		C	D
> Customer Table		   Amount Table	
> Parent ID	Cust #		Cust #	Amount
> 2001	AA		AA	5
> 2001	BB		AA	5
> 2001	CC		AA	5
> 2001	DD		AA	5
> 2001	EE		AA	-5
> 2001	FF		AA	-5
> 2001	GG		BB	3
> 			BB	3
> 			BB	-3
> 				
> Summary				
> 				
> AA - I want to be able to count if "AA" in Cust table (Column B) then count 
> positive amount minus negative amount in amount table (Column D). In this 
> case the answer is "2"				
> 
> BB - The same thing with "BB". The answer is "1"				
> Thank you
> John
> 

With AA in C2 and your Amount Table in A1:B10 (headers in row 1):

=SUMPRODUCT(($A$2:$A$10=C2)*SIGN($B$2:$B$10))
0
Glenn
11/19/2009 7:43:49 PM
Didn't Jacob and I already answer a similar question?

In E2:
=SUMPRODUCT((C$2:C$20=B2)*(SIGN(D$2:D$20)))

Copy down as desired.
-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"John" wrote:

> Hi All,
> 
> I have 2 tables one is cust table and the other one is amount table.
> 
> A	B		C	D
> Customer Table		   Amount Table	
> Parent ID	Cust #		Cust #	Amount
> 2001	AA		AA	5
> 2001	BB		AA	5
> 2001	CC		AA	5
> 2001	DD		AA	5
> 2001	EE		AA	-5
> 2001	FF		AA	-5
> 2001	GG		BB	3
> 			BB	3
> 			BB	-3
> 				
> Summary				
> 				
> AA - I want to be able to count if "AA" in Cust table (Column B) then count 
> positive amount minus negative amount in amount table (Column D). In this 
> case the answer is "2"				
> 
> BB - The same thing with "BB". The answer is "1"				
> Thank you
> John
> 
0
Utf
11/19/2009 8:07:01 PM
Glenn wrote:
> John wrote:
>> Hi All,
>>
>> I have 2 tables one is cust table and the other one is amount table.
>>
>> A    B        C    D
>> Customer Table           Amount Table   
>> Parent ID    Cust #        Cust #    Amount
>> 2001    AA        AA    5
>> 2001    BB        AA    5
>> 2001    CC        AA    5
>> 2001    DD        AA    5
>> 2001    EE        AA    -5
>> 2001    FF        AA    -5
>> 2001    GG        BB    3
>>             BB    3
>>             BB    -3
>>                
>> Summary               
>>                
>> AA - I want to be able to count if "AA" in Cust table (Column B) then 
>> count positive amount minus negative amount in amount table (Column 
>> D). In this case the answer is "2"               
>>
>> BB - The same thing with "BB". The answer is "1"               
>> Thank you
>> John
>>
> 
> With AA in C2 and your Amount Table in A1:B10 (headers in row 1):
> 
> =SUMPRODUCT(($A$2:$A$10=C2)*SIGN($B$2:$B$10))


Missed your column headings:

=SUMPRODUCT(($C$2:$C$10=B2)*SIGN($D$2:$D$10))
0
Glenn
11/19/2009 8:29:43 PM
Hi Luke,

You guys did but this one is a little different. I want to be able to write 
like this:
=SUMPRODUCT(($C$2:$C$20=$B$2:$B$20)*(SIGN(D$2:D$20))) 

this one does not work because I change "B2" to "B2:B20".

"Luke M" wrote:

> Didn't Jacob and I already answer a similar question?
> 
> In E2:
> =SUMPRODUCT((C$2:C$20=B2)*(SIGN(D$2:D$20)))
> 
> Copy down as desired.
> -- 
> Best Regards,
> 
> Luke M
> *Remember to click "yes" if this post helped you!*
> 
> 
> "John" wrote:
> 
> > Hi All,
> > 
> > I have 2 tables one is cust table and the other one is amount table.
> > 
> > A	B		C	D
> > Customer Table		   Amount Table	
> > Parent ID	Cust #		Cust #	Amount
> > 2001	AA		AA	5
> > 2001	BB		AA	5
> > 2001	CC		AA	5
> > 2001	DD		AA	5
> > 2001	EE		AA	-5
> > 2001	FF		AA	-5
> > 2001	GG		BB	3
> > 			BB	3
> > 			BB	-3
> > 				
> > Summary				
> > 				
> > AA - I want to be able to count if "AA" in Cust table (Column B) then count 
> > positive amount minus negative amount in amount table (Column D). In this 
> > case the answer is "2"				
> > 
> > BB - The same thing with "BB". The answer is "1"				
> > Thank you
> > John
> > 
0
Utf
11/20/2009 2:57:02 PM
That formula works fine.  It does exactly what you asked it to do.
For any row where the content of column B equals the content of column C in 
the same row, the sign of the content of row D is added to the total.
If that wasn't what you wanted to do, you needed a different formula.

You asked a different question originally, and Luke and Jacob answered that 
question, but it isn't clear what you now want to do.
--
David Biddulph

"John" <John@discussions.microsoft.com> wrote in message 
news:7AA8E6A5-8C1B-43E5-B609-2FAC449ADACE@microsoft.com...
> Hi Luke,
>
> You guys did but this one is a little different. I want to be able to 
> write
> like this:
> =SUMPRODUCT(($C$2:$C$20=$B$2:$B$20)*(SIGN(D$2:D$20)))
>
> this one does not work because I change "B2" to "B2:B20".
>
> "Luke M" wrote:
>
>> Didn't Jacob and I already answer a similar question?
>>
>> In E2:
>> =SUMPRODUCT((C$2:C$20=B2)*(SIGN(D$2:D$20)))
>>
>> Copy down as desired.
>> -- 
>> Best Regards,
>>
>> Luke M
>> *Remember to click "yes" if this post helped you!*
>>
>>
>> "John" wrote:
>>
>> > Hi All,
>> >
>> > I have 2 tables one is cust table and the other one is amount table.
>> >
>> > A B C D
>> > Customer Table    Amount Table
>> > Parent ID Cust # Cust # Amount
>> > 2001 AA AA 5
>> > 2001 BB AA 5
>> > 2001 CC AA 5
>> > 2001 DD AA 5
>> > 2001 EE AA -5
>> > 2001 FF AA -5
>> > 2001 GG BB 3
>> > BB 3
>> > BB -3
>> >
>> > Summary
>> >
>> > AA - I want to be able to count if "AA" in Cust table (Column B) then 
>> > count
>> > positive amount minus negative amount in amount table (Column D). In 
>> > this
>> > case the answer is "2"
>> >
>> > BB - The same thing with "BB". The answer is "1"
>> > Thank you
>> > John
>> > 


0
David
11/20/2009 5:04:20 PM
Reply:

Similar Artilces:

Sumproduct with multiple date criteria
Having a tough time with this one. Sheet 1 Column A = Start Date, Column B = End Date, Column C = Quantity. Sheet 2 Row A = Start Date, Row B = End Date. I would like Row C to sum quantity from sheet 1 where ever the two date ranges intersect. The date ranges on sheet 2 represent the beginning and ending of a week (Mon-Sun). Sheet 1 Column A Column B Column C 01JAN2010 24JAN2010 1,000 Sheet 2 Row A 04JAN2010 11JAN2010 18JAN2010 25JAN2010 Row B 10JAN2010 17JAN2010 24JAN2010 31JAN2010 Row c 1,000 1,000 1,000 0 Do the Sheet 2 Star...

sumproduct 12-29-09
Hi, I have a format as below, but it seems that <>"h" is not a correct expression when I want to choose all rows except those including "h" in column J SUMPRODUCT((DB!$AB$2:$AB$20000=D$1)*(DB!$J$2:$J$20000<>"h")*(DB!$M$2:$M$2000="p")*(DB!$V$2:$V$20000)) Thanks a lot! -- Lowan That will exclude all rows in J2:J20000 that contain *only* the single letter h. Is that what you intended? -- Biff Microsoft Excel MVP "Lowan Chan" <LowanChan@discussions.microsoft.com> wrote in message news:30EA9061-F2...

SUMPRODUCT to substiute array formula
I have this formula =SUM(IF(ISERROR(A1:A100),0,A1:A100)) entered as an array formula, to sum a column even if some of the cells contains error values. Now I wonder if it could be done using SUMPRODUCT or any other functions, to avoid the array formula? Jan Try this (normally entered): =3DSUMPRODUCT(--(NOT(ISERROR(A1:A100))),A1:A100) Hope this helps. Pete On Feb 26, 8:42=A0am, "Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote: > I have this formula > > =A0 =A0 =A0 =3DSUM(IF(ISERROR(A1:A100),0,A1:A100)) > > ente...

Forecasting using SUMPRODUCT and dates
I am having a problem with counting data that is within a certain date. for example, I have 4 columns. SERVERNAME, LOCATION, TYPE LEASEENDDATE. I am currently using SUMPRODUCT to count the number o servers I have for a particular location and type. A B C D SERVERNAME LOCATION TYPE LEASEENDDATE Server1 MN Web 6/30/2004 Server2 CA App 8/15/2004 SUMPRODUCT((B2:B200="MN")*(C2:C200="Web")) On another sheet, I would like to forecast of how many servers I hav that are not expired...

SumProduct basics
I'm trying to get started with User-Defined Functions. For the sake of learning, I want to create a UDF that acts same as the built-in SumProduct function. So, the two arguments would be two ranges, range1 and range2. The first cell.value in range1 would be multiplied with the first cell.value in range2 and so on - and then all added together at the end. I thought I'd start by using a for loop to go through each cell in range1 but now I'm not sure what is the best/most efficient method to pair the values in range1 with their corresponding values in range2. How should t...

SumProduct not right
This formula is summing ALL the incidences in the N column of what's in E2. I want it to sum ONLY those that are in E2 but also also matching what's in A3. =SUMPRODUCT(--(Data!$E$3:$E$5000=$A$3)--(Data!$R$3:$R$5000=E2),Data!$N$3:$N$5000) I can't figure out why it's not working, because a similiar formula works for other situations. Thanks, Steve On Fri, 8 Jan 2010 13:49:01 -0800, Steve <Steve@discussions.microsoft.com> wrote: >This formula is summing ALL the incidences in the N column of what's in E2. I >want it to sum ONLY those that are...

SUMPRODUCT() yielding #NUM!
Bob Phillips & Glen helped me out with a SUMIF() question I posted earlier today. Both of you recommended that I use this formula: =SUMPRODUCT(--(YEAR(PayDate)=YEAR(TODAY())),LWOP) (I changed the name of the range from Date to PayDate so I don't use a reserved function name.) Now I am getting a #NUM! errror. I looked in help and it said this happens when you have non-numeric data. I've gone through the entire columns in question (PayDate and LWOP) and deleted the values from all cells that should be blank. I just have numbers, dates and the column headings. ...

sumproduct!!!
Hi, In the first sheet, i have the following data S INR 2000 17.6 P SAR 300 30.6 P USD 100 38.4 P USD 200 76.8 S GBP 100 71.5 S GBP 200 143 S EUR 100 47 P AED 200 20.9 In the second sheet it is to be sorted by formula, not by pivot table i made a formula but it is giving wrong result, if somebody helps i this matter much obliged. the formula i tried i =IF(Sheet1!A1:A14="P",IF(Sheet1!B1:B14="USD",SUM(Sheet1!C1:C14,))) I want the result is 300. I know instead of the SUM something els should do. thanks in advance nowfa -- Message posted from http://www.ExcelForum.com =S...

Sumproduct #16
I have 3 columns of data, NAME, DATE,VALUE NAME Date Text Carlos 1/1/4 10 Carlos 2/1/4 20 Carlos 5/6/4 30 Carlos 6/6/4 40 Peter 5/5/4 50 Peter 20/6/4 60 The forumla that I need to use is Look for CARLOS in the database, and retrieve VALUE when NAME is CARLO and DATE is the LATEST (for carlos of course). In this example if I look for Carlos the data I want is 40 If I look for Peter the data I want should be 60 The formula I tried was =sumproduct(NAMES=Carlos)*(DATES=MAX(DATES)),VALUE) I think it is because it doesn't match the latest date for carlos. ...

sumproduct #2
Hi, This looks sooo right to me, but gets an error #VALUE! Not sure where it's wrong. Any help would have me a lot more hours staring at the screen: =SUMPRODUCT(DUMP!A:A=A13)*(DUMP!B:B=$A$1)*(DUMP!E:E) ==> #VALUE! I have tried re-writing the formula as: =SUM(IF((DUMP!A:A=A13)+(DUMP!B:B=$a$1),1,0)*DUMP!E:E)==> 0 (but should be a value greater than 0) The value A13 exists in Dump, column A, more than once. The value A1 exists in Dump, column B, more than once. Numerical data exists in Dump, column E. What am I doing wrong?? "Carole Kaufman" <carolek@ix.netcom.com&g...

Sumproduct accross multiple sheets
=SUMPRODUCT('Deacon, Carrie'!B4:B35,'Deacon Carrie'!C4:C35)+SUMPRODUCT('Deacon, Carrie'!B4:B35,'Deacon Carrie'!D4:D35)+SUMPRODUCT('Deacon, Carrie'!E4:E35,'Deacon Carrie'!F4:F35) I'm using the formula above to calculate some stats for a call center. On a master "group summary page", I need to get the sum of the result of this formula from 20 worksheets. Other than duplicating the formul and changing the names, is there a faster way to do this? The cel references are identical for all, worksheets just the names change. The only ...

array formulas-sumproduct and average
Hello, I need hel. I am using this formula =SUMPRODUCT(--(E2:E289=1),--(AVERAGE (H2:H289))) I don't know what's wrong with it. I need to find the average for column H in relation to 1(under 2 yrs) in Column E. Hope this makes sense. Thanks, Becky Try: =AVERAGE(IF(E2:E289=1,H2:H289)) Array-enter the formula with CTRL+SHIFT+ENTER -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Becky" <Becky@discussions.microsoft.com> wrote in message news:38BA387B-4DA3-4274-9CB5-CCC7E976DDAE@microsoft.com... > Hell...

SUMPRODUCT Help 12-02-09
I apologize for my 3rd question w/in two days, but every time one thing works, another thing does not. In our survey- we are trying to find data in regard to our students (who are in different programs) and what classes are beneficial. Our students can identify themselves in up to 3 different programs out of 6 total programs (coded 1 - 6): (Columns V, W, X) and then can identify up to 3 different beneficial classes (Columns P, Q, R). Each row is a different student. The question is: How many students who identified as '2' also said class 'x' is beneficial? ...

SUMPRODUCT with two criteria?
I am SUMPRODUCT to count the number of items in a list that are "green", except for those which are also "bad". It looks like this: =SUMPRODUCT(--(ISNUMBER(SEARCH("green",C$3:C$100))))-SUMPRODUCT(-- (ISNUMBER(SEARCH("bad",F$3:F$100)))) Unfortunately, "bad" in F3:F100 can be green, red, or blue! How can I adjust this so I get: = (the number of "green" in C3:C100) - (the number of "green" in C3:C100 with "bad" in F3:F100) What functions do I need to make this work? Ed In the 2nd SUMPRODUCT just add another arr...

Sumif or Sumproduct 2 criterias not working
I used the following formula: =SUMPRODUCT(--('PO LIST'!H3:H88="Calibration"),--('PO LIST'!E3:E88="Jan"),('PO LIST'!C3:C88)) The first part looks for "Calibration", then I need it to look for the month "Jan" and when it finds those two to calculate C3:C88. I think my issue is with the field "MONTH" that contains a formated versus ("Jan"), off the column next to it with "DATE" i.e. 01/05/04 - is it not recoginizning "Jan" as a word or does it still look at this field as a date, if so h...

Help with complex SUMPRODUCT formula
Hi, Getting this SUMPRODUCT forumula to work has me stumped. For range: $E$3:$E$352 is not blank For range: $S$3:$S$352 is equal to or greater than $P$3:$P$352 *2 (cells where P is => 2x S) SUM Range:$S$3:$S$352 I've come up with this formula, but clearly it does not work: =SUMPRODUCT(--($E$3:$E$352<>0),(($S$3:$S$352>=($P$3:$P$352*2))),($S$3:$S$352)) Additionally, I need a derivation of this forumula which will count (rather than sum) the number of cells in S which meet the first two criteria. I tried this, but it does not work: =SUMPRODUCT(--($E$3:$E$352<>0),--(($...

Spin Box Date and Sumproduct
Is it possible to use a date (created via an INDEX calculation) within a SUMPRODUCT calculation? I use a SUMPRODUCT calculation to generate headcount figures off monthly master sheets as per the following: =(SUMPRODUCT(--(Apr09!$O$1:$O$6000="XN01"),(Apr09!$CI$1:$CI$6000))). However, I would like to now change the date source of the data based on a month name generated by a Spin Box so that when the month changes fropm Apr09 to May09, the SUMPRODUCT calculation refers to the sheet of May instead. So how do you join the '$O$1:$O$6000' part to the cell containi...

sumproduct in code
I am using code to try and populate a table. The table looks like AA AB 1 2 1 3 2 I am using code to put a sumproduct in AB2 and AB3 (and further as necessary). Currently the line of code I am using is: cells (r+1, "ab").formula = "=sumproduct((" & causecats.address & "=" & _ cells(r+1, "aa") & ")*(" & cause.columns(3).address & "))" When I look at what is showing up in cell AB2, I see: =sumproduct(($ah$2:$ah$32=1)*($ak$2:$ak$32)) The ranges are correct. The problem appears to be that the value,...

SUMPRODUCT with conditions 04-28-10
Hi All I have a formula which is working as follows: =SUMPRODUCT((Data1!B4:B52="Testing")*(Data1!D4:D52<>"")) This basically counts the numbers of values in a column against a set value. So for me it counts the number of projects that have dates against the 'testing' phase. I need to amend this to only count the value if the phase is currently in progress. This would basically be if D4:D52 < Today() and E4:E52 > Today(). Unfortunately I'm having great difficulty putting this in to a formula. Could someone please assist? Tha...

SUMPRODUCT Question #4
Hi, I have a SUMPRODUCT with multiple criteria and wish to add another condition using OR ,,,,,, e.g. A B C D Pens 18/10/2005 ABC 25 Pens 18/10/2005 BCD 10 Pens 18/10/2005 DEF 15 What I want to achieve is to sum the product in col A that matches the date in col B and has a location ( col C ) of either ABC OR BCD and sum col D. What do you mean by a sum of non-numeric data? =SUMPRODUCT((B1:B100=DATEVALUE("18-Aug-2005")*((C1:C100="ABC")+(C1:C100="BCD"))) would count th...

Sumproduct #25
In sheet1, I have 2 columns, column A is weeks numbered 1:52, & column B are numbers. Ex: column A38 is the week number "38", column B38 has "25". My report is sheet2. When I enter a week number in A1, I want the corresponding number from sheet1, column B (in this example, "25") to display in column B1. No matter what week number I enter, I need the correct corresponding week number from sheet one. Thanks for any help. Hi, I presume 'Ex' meant Example, and '38' was in Cell A38 and '25' was in cell B38 In cell B1 of Sheet2 pu...

Problems with sumproduct and dates
Hi, I'm a newbie in sumproducts but have found it very functional. However I have a probelem with using a data as criteria in an array. The formul doesn't work when testing for the date (in the example 18-10-2004), bu when I test the formula with anything else - e.g. a text string - i works well. Do you have a solution? =SUMPRODUCT(--(N2:N1000="Meeting");--(O2:O1000="18-10-2004")) Thanks in advance -- Krelle ----------------------------------------------------------------------- Kreller's Profile: http://www.excelforum.com/member.php?action=getinfo&user...

SUMPRODUCT Question...
I think a quick question here... I have a rather lengthy SUMPRODUCT formula that I want to leave a blan cell if the product of my criteria is zero. Is there a way to do this? It seems to me that I have seen folks post here about a IFISERROR o something like that, but I thought that was to return a blank i VLOOKUP #N/A result. This is pretty similar but I can't seem to figur out how to get it done. Thanks, PZa -- PokerZa ----------------------------------------------------------------------- PokerZan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2348 V...

Sumproduct with "contains" variable
When you filter a column in Excel 2007, there is an option of "contains" to choose. Is it possible to use "contains" in a sumproduct() formulae as one of the arrays or do I have to list the possibilities in each of the variables? Surely there must be a way to use wildcard characters of ? or * or something to pass this on in the formulae. If so, how? Thanks, -- Lee Coleman =sumproduct(--(isnumber(search("sometext",a1:a10))), --(b1:b10="somethingelse"), (c1:c10)) Lee wrote: > > When you filter a column in Exce...

Another SumProduct Question
Here's a simple example of something I'm trying to figure out. A B 1 5 1 7 2 4 2 9 Can I calculate (5*4)+(7*9)=83 using a sumproduct? I tried this but it doesn work for what I think is an obvious reason. SumProduct(((B1:B4)*(A1:A4=1)),((B1:B4)*(A1:A4=2))). I believe this isn't working because there is never a case where A1:A4=1 AND A1:A4=2. Therefore I get the answer = 0. I'm also having a hard time trying to figure out how I would do this in a loop of some kind as an alternative. Can I define B1:B2 as a range and B3:B4 as a range and pass them to a...