Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup and SUM Value from Another Workbook
Since you mentioned totaling "E and/or F",
how about assigning particular cells to contain your variable criteria. Say, G1 = Date (text) G2 = Name G3 = E (to sum Column E) G4 = F (to sum Column F) And try this formula: =SUMPRODUCT((C2:C100=G1)*(D2:D100=G2)*((G3="E")*E2 :E100+(G4="F")*F2:F100)) You must make sure that G1 and G2 match *exactly* with the data in Columns C and D. Either G3 or G4 may be left blank to total only one or the other of the columns. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "ken8795" wrote in message ... Pardon my inexperience please but I am looking for what seems would be a fairly simple formula, however have thus far had no luck. I have a workbook that has data saved as follows... Column C is a TEXT field with a Month/Yr (JAN06, FEB06 etc) Column D is a TEXT field with a Name Column E is a NUMERIC field Column F is a NUMERIC field The worksheet can contain multiple rows with the same MONTH/YR and/or NAME. This file is generated from my billing system. The number of rows is not static. What I want to do as an example is to SUM the value (E) and or (F) by "Month" (C) (Text) for all with the same name (D) in a single cell within another Workbook. My intention is to be able to run and save the report from my billing system and have the separarate worksheet to summarize the data. Any assistance would be greatly appreciated! -- ken8795 ------------------------------------------------------------------------ ken8795's Profile: http://www.excelforum.com/member.php...o&userid=36411 View this thread: http://www.excelforum.com/showthread...hreadid=561851 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup and SUM Value from Another Workbook
I just wanted to be sure to thank both of the individuals that responded to my posting, as both your suggestions were exactly what I needed. I really appreciate the help:) -- ken8795 ------------------------------------------------------------------------ ken8795's Profile: http://www.excelforum.com/member.php...o&userid=36411 View this thread: http://www.excelforum.com/showthread...hreadid=561851 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup and SUM Value from Another Workbook
And WE appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "ken8795" wrote in message ... I just wanted to be sure to thank both of the individuals that responded to my posting, as both your suggestions were exactly what I needed. I really appreciate the help:) -- ken8795 ------------------------------------------------------------------------ ken8795's Profile: http://www.excelforum.com/member.php...o&userid=36411 View this thread: http://www.excelforum.com/showthread...hreadid=561851 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup and SUM Value from Another Workbook
Your advice worked perfectly on my test file. I went to use the function with my real data file and received an error message... Microsoft Excel Excel cannot complete this task with available resources. Choose less data or close other applications. The only option available is the OK button which closes Excel. Excel is the only application running. The real data sheet has just under 8100 rows, so I attempted to reduce the outside range to 8100, and continue to receive the same error whenever I attempt to enter the formula in. The final formula I am using is... =SUMPRODUCT(--('C:\Documents and Settings\Ken\My Documents\[producerytd.xls]Sheet1'!$A$2:$A$8100=G4)*(--('C:\Documents and Settings\Ken\My Documents\[producerytd.xls]Sheet1'!$M$2:$M$8100=G5)*(--('C:\Documents and Settings\Ken\My Documents\[producerytd.xls]Sheet1'!$Q$2:$Q$8100)))) Any Ideas -- ken8795 ------------------------------------------------------------------------ ken8795's Profile: http://www.excelforum.com/member.php...o&userid=36411 View this thread: http://www.excelforum.com/showthread...hreadid=561851 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup and SUM Value from Another Workbook
What I would start off doing, would be to put this formula in the *actual*
sheet that the real data was located in, to eliminate all the path info. Then I would eliminate all the *excess* operators, namely, there's *no* need for both the double unary and the asterisk. This would leave the formula on the *real* data sheet (producerytd) as: =SUMPRODUCT(($A$2:$A$8100=G4)*($M$2:$M$8100=G5)*$Q $2:$Q$8100) You'd of course have to add the criteria of G4 and G5 to the G4 and G5 of *this* sheet. NOW, what do you get with this test? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "ken8795" wrote in message ... Your advice worked perfectly on my test file. I went to use the function with my real data file and received an error message... Microsoft Excel Excel cannot complete this task with available resources. Choose less data or close other applications. The only option available is the OK button which closes Excel. Excel is the only application running. The real data sheet has just under 8100 rows, so I attempted to reduce the outside range to 8100, and continue to receive the same error whenever I attempt to enter the formula in. The final formula I am using is... =SUMPRODUCT(--('C:\Documents and Settings\Ken\My Documents\[producerytd.xls]Sheet1'!$A$2:$A$8100=G4)*(--('C:\Documents and Settings\Ken\My Documents\[producerytd.xls]Sheet1'!$M$2:$M$8100=G5)*(--('C:\Documents and Settings\Ken\My Documents\[producerytd.xls]Sheet1'!$Q$2:$Q$8100)))) Any Ideas -- ken8795 ------------------------------------------------------------------------ ken8795's Profile: http://www.excelforum.com/member.php...o&userid=36411 View this thread: http://www.excelforum.com/showthread...hreadid=561851 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|