Combining SUMPRODUCT conditions 2

[NumberCrunchingMonky]

I have a SUMPRODUCT formula that's not returning all the data I need it to and I suspect it's because I'm using AND/OR incorrectly.

The formula reads across four spreadsheets (two named ranges per sheet), and the formula operation specific to each of the four sheets is seperated with a "+" (for "or&quot. The data I'm trying to pull can be found on more than one sheet but my formula seems to sum/return only values from the first data set to which the coditions are met.

Here is my Formula:
=SUMPRODUCT((Range1=$B5)*(Range2=D$4)*(HoursSheet1)+(Range3=$B5)*(Range4=D$4)*(HoursSheet2)+(Range5=$B5)*(Range6=D$4)*(HoursSheet3)+(Range7=$B5)*(Range8=D$4)*(HoursSheet4))

I would appreciate any feedback.

Thanks,

Monky

 

[bluedragon2]

If I am understanding your question correctly:

You have 4 sheets that contain the same 3 types of data. You want to look at all 4 sheets for your criteria as it could be in any of those sheets. If this is the case, then you will want to do the following:

=SUMPRODUCT((Range1=$B5)*(Range2=D$4)*(HoursSheet1))+SUMPRODUCT((Range3=$B5)*(Range4=D$4)*(HoursSheet2))+SUMPRODUCT((Range5=$B5)*(Range6=D$4)*(HoursSheet3))+SUMPRODUCT((Range7=$B5)*(Range8=D$4)*(HoursSheet4))

What this does is looks at each array for the criteria you define and then adds the results of those 4 lookup's.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

You could also use:

=SUMPRODUCT(((Range1=$B5)*(Range2=D$4)*(HoursSheet1))+((Range3=$B5)*(Range4=D$4)*(HoursSheet2))+((Range5=$B5)*(Range6=D$4)*(HoursSheet3))+((Range7=$B5)*(Range8=D$4)*(HoursSheet4)))

It takes out the additional SUMPRODUCTS, but does seperate each lookup...



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

...and how did I KNOW, without a doubt, that Blue would jump on this one! ;-)

You da FORMULA man!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bluedragon2]

I just applied for membership into the squaring a circle forum...

I just hope you don't have to take a test to get in




[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

You'll do just fine. I am a member in NAME only HA!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[NumberCrunchingMonky]

Thanks, fellas, for your input and examples. It now works.
Monky