Sumif

[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

 

[Guest]

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"))

 

[RagDyer]

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.

 

[Scorpvin]

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

 

[RagDyer]

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]

You are awesome! Thanks for the help.

 

[Scorpvin]

Can how do you include a wildcard character in an array formula such as
*
Example Rectang*

 

[RagDyeR]

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*

 

[Scorpvin]

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*

 

[Ragdyer]

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!

 

[Guest]

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.

 

[Scorpvin]

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

 

[RagDyeR]

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!