Struggling with Sumproduct

Hi

Reposting as still and issue.

I have a spreadsheet with accounts transaction data where column a = date, 
column b = nominal code, Column c = department name, column d = department 
number, column e = amount.

I need to query the columns to sum transactions for a particular month for a 
particular nominal code, for a particular department.
The department name is only included to identify the department number, and 
will not be used.

My initial thoughts  are 
=SUMPRODUCT((Sheet1!A2:A16202=MONTH(6))*(Sheet1!B2:B16202="4000")*(Sheet1!D2:D16202=1)*(Sheet1!E2:E16202))

However it returns a zero value.

Once i have achieved this I will need to increase the range to include 
currently empty cells, as when the accounts data is refreshed it will 
increase the range of occupied cells, which i believe i could proabaly 
achieve by including the formula in an If statement.

Any help greatly appreciated.
0
philn (6)
1/8/2009 3:08:28 PM
excel 39879 articles. 2 followers. Follow

4 Replies
645 Views

Similar Articles

[PageSpeed] 39

Notice a couple of changes
=SUMPRODUCT((month(Sheet1!A2:A16202)=6)*(Sheet1!B2:B16202="4000")*(Sheet1!D2:D16202=1)*Sheet1!E2:E16202)


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"philn" <philn@discussions.microsoft.com> wrote in message 
news:EC081FC6-7241-4BBA-9A30-70C22969FE03@microsoft.com...
> Hi
>
> Reposting as still and issue.
>
> I have a spreadsheet with accounts transaction data where column a = date,
> column b = nominal code, Column c = department name, column d = department
> number, column e = amount.
>
> I need to query the columns to sum transactions for a particular month for 
> a
> particular nominal code, for a particular department.
> The department name is only included to identify the department number, 
> and
> will not be used.
>
> My initial thoughts  are
> =SUMPRODUCT((Sheet1!A2:A16202=MONTH(6))*(Sheet1!B2:B16202="4000")*(Sheet1!D2:D16202=1)*(Sheet1!E2:E16202))
>
> However it returns a zero value.
>
> Once i have achieved this I will need to increase the range to include
> currently empty cells, as when the accounts data is refreshed it will
> increase the range of occupied cells, which i believe i could proabaly
> achieve by including the formula in an If statement.
>
> Any help greatly appreciated. 

0
dguillett1 (2487)
1/8/2009 3:15:25 PM
Phil,

MONTH(Sheet1!A2:A16202)=6

would be the way to specify the month.

And, maybe, change "4000" to 4000

But a better way for any of this would be to use a pivot table.

HTH,
Bernie
MS Excel MVP


"philn" <philn@discussions.microsoft.com> wrote in message 
news:EC081FC6-7241-4BBA-9A30-70C22969FE03@microsoft.com...
> Hi
>
> Reposting as still and issue.
>
> I have a spreadsheet with accounts transaction data where column a = date,
> column b = nominal code, Column c = department name, column d = department
> number, column e = amount.
>
> I need to query the columns to sum transactions for a particular month for a
> particular nominal code, for a particular department.
> The department name is only included to identify the department number, and
> will not be used.
>
> My initial thoughts  are
> =SUMPRODUCT((Sheet1!A2:A16202=MONTH(6))*(Sheet1!B2:B16202="4000")*(Sheet1!D2:D16202=1)*(Sheet1!E2:E16202))
>
> However it returns a zero value.
>
> Once i have achieved this I will need to increase the range to include
> currently empty cells, as when the accounts data is refreshed it will
> increase the range of occupied cells, which i believe i could proabaly
> achieve by including the formula in an If statement.
>
> Any help greatly appreciated. 


0
Bernie
1/8/2009 3:18:10 PM
Thanks Don noted the changes to both date and value fileds, now works a treat 
so onto the next faze!

"Don Guillett" wrote:

> Notice a couple of changes
> =SUMPRODUCT((month(Sheet1!A2:A16202)=6)*(Sheet1!B2:B16202="4000")*(Sheet1!D2:D16202=1)*Sheet1!E2:E16202)
> 
> 
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "philn" <philn@discussions.microsoft.com> wrote in message 
> news:EC081FC6-7241-4BBA-9A30-70C22969FE03@microsoft.com...
> > Hi
> >
> > Reposting as still and issue.
> >
> > I have a spreadsheet with accounts transaction data where column a = date,
> > column b = nominal code, Column c = department name, column d = department
> > number, column e = amount.
> >
> > I need to query the columns to sum transactions for a particular month for 
> > a
> > particular nominal code, for a particular department.
> > The department name is only included to identify the department number, 
> > and
> > will not be used.
> >
> > My initial thoughts  are
> > =SUMPRODUCT((Sheet1!A2:A16202=MONTH(6))*(Sheet1!B2:B16202="4000")*(Sheet1!D2:D16202=1)*(Sheet1!E2:E16202))
> >
> > However it returns a zero value.
> >
> > Once i have achieved this I will need to increase the range to include
> > currently empty cells, as when the accounts data is refreshed it will
> > increase the range of occupied cells, which i believe i could proabaly
> > achieve by including the formula in an If statement.
> >
> > Any help greatly appreciated. 
> 
> 
0
philn (6)
1/8/2009 3:40:05 PM
Thanks Bernie, unable to use a pivot as dragging data into an existing report 
format

"Bernie Deitrick" wrote:

> Phil,
> 
> MONTH(Sheet1!A2:A16202)=6
> 
> would be the way to specify the month.
> 
> And, maybe, change "4000" to 4000
> 
> But a better way for any of this would be to use a pivot table.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "philn" <philn@discussions.microsoft.com> wrote in message 
> news:EC081FC6-7241-4BBA-9A30-70C22969FE03@microsoft.com...
> > Hi
> >
> > Reposting as still and issue.
> >
> > I have a spreadsheet with accounts transaction data where column a = date,
> > column b = nominal code, Column c = department name, column d = department
> > number, column e = amount.
> >
> > I need to query the columns to sum transactions for a particular month for a
> > particular nominal code, for a particular department.
> > The department name is only included to identify the department number, and
> > will not be used.
> >
> > My initial thoughts  are
> > =SUMPRODUCT((Sheet1!A2:A16202=MONTH(6))*(Sheet1!B2:B16202="4000")*(Sheet1!D2:D16202=1)*(Sheet1!E2:E16202))
> >
> > However it returns a zero value.
> >
> > Once i have achieved this I will need to increase the range to include
> > currently empty cells, as when the accounts data is refreshed it will
> > increase the range of occupied cells, which i believe i could proabaly
> > achieve by including the formula in an If statement.
> >
> > Any help greatly appreciated. 
> 
> 
> 
0
philn (6)
1/8/2009 3:41:01 PM
Reply:

Similar Artilces:

Help, Think I need to use countif or sumproduct
Please try and help me with this one: I have a spreadsheet with the following data columns: A B C D E F No Ac Name Staff Name Area Grade I want a formula to look in the area column and the grade column an tell me how many grade 4 there are working in wales etc. I can easil type in the formula the areas i need each time thats fine just need t know how i can get it to count only the grade 4s in wales etc -- Jai ----------------------------------------------------------------------- JaiD's Profile: http://www.excelfor...

Sumproduct & nagetive time
So far I found I can use this, in column E. =IF(C3<=D3,"+ ","- ")&TEXT(MAX(C3,D3)-MIN(C3,D3),"[m]:ss") To display negative time, whereas before I was using, =IF(D3="","",D3-C3) to leave the column E blank. The 2nd one was good because it would leave the column blank until I made an entry in D, is there a way to use the first Formula and leave the column blank? Also I'm using Sumproduct on another sheet to count the entrees in E. Here is an example. In B, =SUMPRODUCT(--(LEFT(LTC!$F$3:$F$192,4)=OTP!A8),--(LTC!$E$3:$E$192&...

Updating SUMPRODUCT Formula?
Currently, I used the following formula to display how many digits from the previous 3 cells were repeated in the next 3 cells but I in some examples it does not give me the answer I am looking for see examples below: Formula Used: =SUMPRODUCT(COUNTIF(AF575:AH575,AF577:AH577)) Correct Sample #1 AF575:AH575 3,7,0 AF577:AH577 3,5,9 C577=1 correct Incorrect Sample #2 AF575:AH575 3,3,3 AF577:AH577 3,5,9 C577=3, only 1 digit is actually repeated, therefore the answer should be 1 Incorrect Sample #3 AF575:AH575 1,6,3 AF577:AH577 3,3,3 C577=3, only 1 digit is actually repeated, therefore the answ...

Struggling with my insert query...
I've got this insert query running embedded in a VBA statement. The VBA looks at a table and finds the largest number stored in the column BatchNum and then adds 1 to it and stores that new number in the variable lngBatch. It then runs the following SQL (note that the <lngBatch> as seen below is just a placeholder... in the actual code the SQL statment is put into a string and would be "... CaseNum," & lngBatch & vbCrLf & "FROM ..."): INSERT INTO tblRef ( RefNum, BatchNum ) SELECT DISTINCT CaseNum, <lngBatch> FROM tblMain; This statement...

Subtotal
I'm using a sheet with filtered results and have used subtotal to ensure that some averages update dynamically based on filtered results. One of the fields requires me to use =sumproduct to calculate the correct numbers for an average. The following formula works without the filters... =SUMPRODUCT(G8:G34*H8:H34)/SUM(G8:G34) I've adjusted it to read =SUMPRODUCT(G8:G34*H8:H34)/SUBTOTAL(9,G8:G34) The issue I'm faced with is getting the ranges (G8:G34 and H8:H34) to change everytime I change the filter. The list of Function Numbers for Subtotal does not include an option for su...

Sumproduct or ????
I need to calculate 'unreconciled amount' in my check register by looking at column B (deposit or withdrawal), column E (check amount), column F (bank balance) and column G (reconciled). Column G will contain either "R" for reconciled or it will be null. All Uncreconciled (no "R") transactions must be examined to see if they are deposits or withdrawals (column B), subtracting withdrawals from deposits. Can anyone help with a formula? I currently have a helper column that is used to total the unreconciled amounts. (=IF($G278<>"R",I...

SUMPRODUCT vs Arrays
I've been using SUM-IF array formulas since Excel 4. But recently, I've tried to use SUMPRODUCT instead. That is... {=SUM(IF((Name="Smith")*(State="Utah"),Amount,0))} ....does the same thing as... =SUMPRODUCT(0+(Name="Smith"),0+(State="Utah"),Amount) ....but without the inconvience of arrays. However, today I realized that if Amount is wider than one column, SUMPRODUCT fails but SUM-IF works. Am I mising something? Is there a simple way to make SUMPRODUCT work when Amount is a dynamic range that could define any number of columns? Charley ...

Frustrating SUMPRODUCT problem.
This problem is doubly frustrating because I had it nailed down yesterday, then my PC crashed and I lost my formula. And now I can't reproduce it.... I am trying to sum a number of items in a given row on another worksheet (sheet 2), depending on whether the value in the header row in the corresponding column falls between two values (call them A and B) in the header row of my source worksheet (sheet 1). Let me demonstrate by example: Sheet 1 Sheet 2 60 65 70 75 61 63 64 67 71 72 1 X ...

=SUMPRODUCT((Data1!$B$1:$B$500="March")*(Data1!$C$1:$C$500="New York"),(Data1!$E$1:$E$500))
Hi =SUMPRODUCT((Data1!$B$1:$B$500="March")*(Data1!$C$1:$C$500="New York"), (Data1!$E$1:$E$500)) In plain english the formula above means: Sum cells E1 to E500 (last argument) if in B1 to B500 the value is "March" and if in C1 to C500 the value is "New York". SUMPRODUCT is like SUMIF on steroids and it allows you to automate all these reports that you develop with data that you import from a large centralized database, centralized accounting, manufacturing and sales programs or the Internet. The last argument (preceded by a comma) tells Excel what to...

Using wildcards w/ sumproduct
I'm not familiar with wildcards in formulas, and I can't seem to figure out exactly what to do here. If the first 3 letters in cells of column A contain FPC, then sum corresponding rows in column P, then by the total number of cells in column AT where the first 3 letters are FPC. =SUMPRODUCT((LEFT(A1:A20014,3*(closed!$A$3:$A$20003="FPC"))),(closed!$P$3:$P$20003)/SUMPRODUCT((LEFT(A1:A20014,3*(closed!$AT$3:$AT$20003="FPC"))))) Any assistance is greatly appreciated. Thanx, ~Julz Hi if I understood you correctly try =SUMPRODUCT(--(LEFT(closed!$A$3:$A$20003)=&qu...

Sumproduct Wildcard #2
=SUMPRODUCT(--(C6:C20>=38473)*(C6:C20<=38503),--(D6:D20="food"),F6:F20) Expert, Above is the formula i am using, however it will only return a value when only 'food' is contained in the cell. With this formula I have built a simple check-book for my own personal finance. what can i do to this formula to look for 'food' in cells such as ' Giant Food Store' of 'Food Lion'? Thank you, RB One way .. Try replacing: --(D6:D20="food") with: --ISNUMBER(SEARCH("food",D6:D20) in the formula -- Rgds Max xl 97 --- GMT+8, 1� 22&#...

Querying multiple Product Codes in one field using SUMPRODUCT()
Hi All, I have a field named Product_Code in my database. Have another field called country_code, and a last one named Shipment_Amt$. My draft formula is: =sumproduct(--(Product_code=B15), --(Country_code=$C$2)) How do i perfect my sumproduct formula to extract out multiple product codes, look for a particular country code and sum up all the shipment amt? Thanks... try =sumproduct(--(Product_code=B15), --(Country_code={$C$2,$c$5}),shipmentamt) -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Tan" <Tan@discussions.microsoft.com> wrote in message news:13C675...

sumproduct function combined with named range?
This is a multi-part message in MIME format. ------=_NextPart_000_008D_01C3ECB5.1ABC2740 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable In my scheduling project (Excel 2002), I am creating cell dropdown = validation to enter in work and time codes. A calculation I have to deal = with the data (thanks to other newsgroup gurus!) is this: = =3DSUMPRODUCT((J42:J407=3D{"E","D","N","A","S","U","M","B","V","ST"})*($A= $42:$A$407<TODAY())). In ...

Struggling with Data Migration Manager
Hi All, I'm trying to import data from Goldmine to MS CRM 4.0 using the Data Migration Manager. I'm led to beleive that if I import both account and contact data at the same time, that the DMM can correctly tie up the relationships between the two (primary contact & parent customer). However, I receive a "The Conversion Job has failed" every time i try to do it, even with only a couple of line items being imported, with no unusual characters etc. I'm 99% sure that it's to do with the relationship linking. Is there any how-to guide on the DMM around relat...

SUMPRODUCT with date range
I need help with a sumproduct formula that will sum with three difference types of criteria and also use a date range? Any suggestions? >Any suggestions? Yes Give us some specific details. What are the ranges and what are the criteria and what range needs to be summed? -- Biff Microsoft Excel MVP "NMK" <NMK@discussions.microsoft.com> wrote in message news:31536676-CE78-4FA0-B893-773BE2FB967A@microsoft.com... >I need help with a sumproduct formula that will sum with three difference > types of criteria and also use a date range? Any sugges...

Sumproduct Question Working With Start and End Date Range
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 both the Start Date ...

sumproduct #23
the sumproduct function is not available in my excel 2003, why? i have the czech version, is there another verb for sumproduct in czech? Try this to find the translation: Open excel hit alt-f11 to get to the VBE hit ctrl-g to see the immediate windows type this and hit enter: range("a1").formula = "=sumproduct(--(b1:b10=1))" Then back to excel and look at A1 of that worksheet. mg wrote: > > the sumproduct function is not available in my excel 2003, why? > i have the czech version, is there another verb for sumproduct in czech? -- Dave Peterson Thanx for ...

SumProduct over multiple sheets
Hello The below formula does one sheet. I have ten. Is there a way to have it sum all ten for me that does not require a long formula Thanks for your help =SUMPRODUCT(('Line7'!$C$15:$C$114=C7)*('Line7'!$G$15:$G$114='Numbers'!$S$9)*('Line7'!$AF$15:$AF$114)) I'd use 10 different formulas. Then sum them in an eleventh formula. In fact, if you put each of those formulas in a dedicated cell (say A1 of each individual sheet), you could use a formula like: =sum('line1:line10!a1) As long as all the other sheets are between line1 and line10. Jack wr...

Count between two dates using sumproduct
Using Excel 2003, A5:A1000 is a range of dates covering 12 months, 1/1/10 through 12/31/10; E5:E1000 is a range of names, ie Smith. I need to count the number of times Smith occurs for each month, ie January, February, etc. I have the following formula, {=SUMPRODUCT(--(A5:A1000>=1/1/2010),--(A5:A1000<=1/31/2010),--(E5:E1000)="Smith")}, but am getting a value error. What am I missing? Try this amendment, normal ENTER will do: =SUMPRODUCT(--(A5:A10>=--"1/1/2010"),--(A5:A10<=--"1/31/2010"),--(E5:E10="Smith")) Success? wave it, hit Y...

SumProduct 12-11-09
I am trying to creat a roll up sheet. on my data sheet I have multiple columns of data. F K L R 51160 2 6 3 51160 1 4 1 51162 0 5 2 51162 1 0 3 I am trying to create a formula that will search column F for matching numbers and then add all cooresponding data in column K, L, and R. So cell A1 of my roll up sheet for "51160" will show the total of 17... Any ideas? Thanks Try =SUM(SUMIF(F1:F10,51160,INDIRECT({"K1:K10","L1:L10","R1:R10"}))) -- Jacob "HeatherMichelle" wrote: >...

Embed a 'match' statement in sumproduct?
Excel 2007 I'm trying to help a coworker who wants to generate a few summary pieces of data (like a pivot table, but for only a few categories). The raw data includes both valid and invalid product codes, so these summaries need to exclude the rows with invalid codes and their associated data. Sheet1: Location of this formula. Formula is looking for a total for a specific location Sheet2: Raw data including valid and invalid product code sales Sheet3: List of invalid product codes This formula works, but does not exclude the invalid products: =SUMPRODUCT(('Sheet 2...

sumproduct--counting--zero--blank cells
I'm using these formula to count, =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) ........etc how do i get it so bank cells are excluded from the count. The way it is now, they are counted in the 0 to 10 range... Thanks Jeremy -- Message posted via http://www.officekb.com COUNTBLANK(range) "jeremy via OfficeKB.com" wrote: > I'm using these formula to count, > > =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) > =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) > ........etc > how do...

SumProduct Between Dates
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 Hi, =SUM...

SUMPRODUCT 05-06-10
Hello, I've used this formular to find work piece that gets done per department. =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532")) Department 14532 was not picked up even though other department's number was correctly counted. But if I put a letter, say like C (14532C) and adds C to my formular as in =SUMPRODUCT(('Q2'!E13:E150="Done")*('Q2'!G13:G150="14532C")) then it picks up how many pieces were done for this department. I suspect this is in my cell format but I could not figure out wh...

Struggling to connect Entourage 2008 to Exchange 2003
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Email Client: Exchange This may have been covered already, but I have a Macbook Pro running Mac OSX 10.4.11 I have Entourage 2008 version 13.0.3 (091002) I'm new to the mac world and I'm struggling to connect it to our Exchange 2003 server. <br> I have connected fine using Mail I can access the server via OWA through Safari, but Entourage refuses to connect, What am I doing Wrong : ( > This message is in MIME format. Since your mail reader does not understand this format, some or all of this mess...