|
Sumif
My sumif formula only works if I have the source document open. If I open the file with the sumif first and update links it returns #VALUE! When I open the source document it works automatically. I know the reason is because I'm referencing another workbook. Is there something I need to add to the formula or is there an option in excel I need to adjust? -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=471902 |
Not sure how to make sumif work, but Sumproduct may be an alternative.
As an example, this formula will return the number of times "jeff" appears in range A1:A4 of Book1. =SUMPRODUCT(--('H:\Blakley\Excel\[Book1.xls]Sheet1'!A1:A4="jeff")) "Scorpvin" wrote: My sumif formula only works if I have the source document open. If I open the file with the sumif first and update links it returns #VALUE! When I open the source document it works automatically. I know the reason is because I'm referencing another workbook. Is there something I need to add to the formula or is there an option in excel I need to adjust? -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=471902 |
You can use a combination of SUM and IF in an array formula to return your
values from closed WBs. Post your formula for help. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Scorpvin" wrote in message ... My sumif formula only works if I have the source document open. If I open the file with the sumif first and update links it returns #VALUE! When I open the source document it works automatically. I know the reason is because I'm referencing another workbook. Is there something I need to add to the formula or is there an option in excel I need to adjust? -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=471902 |
RD, Here is the formula. Note the source is an different workbook. =SUMIF('C:\Documents and Settings\Hotspots\My Documents\[Source IFSUM.xls]Sheet1'!$A:$B,"Rectangle",'C:\Documents and Settings\Hotspots\My Documents\[Source IFSUM.xls]Sheet1'!$B:$B) -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=471902 |
Why are you searching 2 columns (A:B) for "Rectangle",
and returning the values from a single Column B? Typo? Anyway, here's the simple syntax to follow in this *array* formula: =SUM(IF(A1:A1000="Rectangle",B1:B1000)) Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, notice that you cannot use entire Column references (A:A) in array formulas. You should be able to in the next version of XL though. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Scorpvin" wrote in message ... RD, Here is the formula. Note the source is an different workbook. =SUMIF('C:\Documents and Settings\Hotspots\My Documents\[Source IFSUM.xls]Sheet1'!$A:$B,"Rectangle",'C:\Documents and Settings\Hotspots\My Documents\[Source IFSUM.xls]Sheet1'!$B:$B) -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=471902 |
You are awesome! Thanks for the help. -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=471902 |
Can how do you include a wildcard character in an array formula such as * Example Rectang* -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=471902 |
One way:
=SUM(IF(LEFT(A1:A1000,7)="Rectang",B1:B1000)) Also array entered. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Scorpvin" wrote in message ... Can how do you include a wildcard character in an array formula such as * Example Rectang* -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=471902 |
What if I have text before and after such as: One Rectangle Five Rectang Three I would think I need some sort of a wildcard character like *Rectang* -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=471902 |
Try this then:
=SUM(IF(NOT(ISERR(SEARCH("rectang",A1:A1000))),B1: B1000)) Also array entered. Will work if "rectang" is anywhere within the cell! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Scorpvin" wrote in message ... What if I have text before and after such as: One Rectangle Five Rectang Three I would think I need some sort of a wildcard character like *Rectang* -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=471902 |
whatever formulas you have will work best/fastest if all data is in the same
workbook. I usually have a worksheet in the active workbook that updates certain columns or information from individual sheets when it opens. Then all the data is in the workbook, ready to work with. "Ragdyer" wrote: Try this then: =SUM(IF(NOT(ISERR(SEARCH("rectang",A1:A1000))),B1: B1000)) Also array entered. Will work if "rectang" is anywhere within the cell! -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Scorpvin" wrote in message ... What if I have text before and after such as: One Rectangle Five Rectang Three I would think I need some sort of a wildcard character like *Rectang* -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=471902 |
Ragdyer- You're are truely the array king! I appreciate all your help. Everyhthing works like I imaged it. Thanks! -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=471902 |
Thanks for the feed-back.
However, if you're going to use this on *large* datalists, since your original formula used full column references (B:B), use this formula instead, which reduces the number of functions within the formula and may run a little faster: =SUM(IF(ISNUMBER(SEARCH("rectang",A1:A1000)),B1:B1 000)) Also array entered with CSE. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Scorpvin" wrote in message ... Ragdyer- You're are truely the array king! I appreciate all your help. Everyhthing works like I imaged it. Thanks! -- Scorpvin ------------------------------------------------------------------------ Scorpvin's Profile: http://www.excelforum.com/member.php...o&userid=27678 View this thread: http://www.excelforum.com/showthread...hreadid=471902 |
| All times are GMT +1. The time now is 07:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com