Sumif

  • Thread starter Thread starter Scorpvin
  • Start date Start date
S

Scorpvin

My sumif formula only works if I have the source document open. If
open the file with the sumif first and update links it returns #VALUE!
When I open the source document it works automatically. I know th
reason is because I'm referencing another workbook. Is there somethin
I need to add to the formula or is there an option in excel I need t
adjust
 
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"))
 
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.
 
RD,
Here is the formula. Note the source is an different workbook.

=SUMIF('C:\Documents and Settings\Hotspots\My Documents\[Sourc
IFSUM.xls]Sheet1'!$A:$B,"Rectangle",'C:\Documents an
Settings\Hotspots\My Documents\[Source IFSUM.xls]Sheet1'!$B:$B
 
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!
==============================================
 
Can how do you include a wildcard character in an array formula such as
*
Example Rectang*
 
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!
=====================================================


message
Can how do you include a wildcard character in an array formula such as
*
Example Rectang*
 
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*
 
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!
 
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-
You're are truely the array king! I appreciate all your help.
Everyhthing works like I imaged it. Thanks!
 
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:B1000))

Also array entered with CSE.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------


message
Ragdyer-
You're are truely the array king! I appreciate all your help.
Everyhthing works like I imaged it. Thanks!
 
Back
Top