SUMPRODUCT on seperate sheet / workbook

[etjohnson81]

I have a report that is generated from an Oracle data base into an Excel spreadsheet for viewing.

I am trying to create a summary page in another workbook as the report generates dynamically.

When I have data on the same sheet, it works fine.

When I reference another sheet or workbook, I get a zero result.

Any ideas?

here is my formula
=SUMPRODUCT(Sheet1!B2:Sheet1!B1084="Florida")*(Sheet1!H2:H1000="Yes")

Thanks
Travis Johnson

 

[AnotherHiggins]

[tt]Sheet1!B2:Sheet1!B10[red]84[/red]
Sheet1!H2:[highlight] [/highlight]H10[red]00[/red][/tt]

Each section of the formula must contain the same number of rows. Also, you don't declare the sheet name in the second part of the second range (I'm not sure this will cause an error).

The easiest (and best, IMHO) way to get a reference to another sheet/workbook is to type in the formula until you get to where the range goes. Instead of typing in the range, browse over to the other sheet/workbook and click/drag to select the desired range. This removes any possibility of typos in the sheet/workbook names.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

You should NEVER need to type a range reference into a formula.
[tt]
=SUMPRODUCT((
[/tt]
then select the workbook/sheet and range, and continue with the formula.



Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[yogia]

Hi Travis Johnson:

Just elaborating what John has so clearly stated ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com