Greetings DBForum Users,

I need a little help with two array formulas. I would like to sum amounts based on: start/end dates, salesman, and product. Since a picture = 1K words I thought it best just to show what I need instead of trying to describe it in detail.

Thanks In Advance...

Hi

For your AND Formula use this instead

=SUMPRODUCT(--(A2:A7>J2)*(A2:A7<J3)*(B2:B7=G4)*(C2:C7=G6)*(D2: D7))

and for your OR Formula you can use this(although there will be a better way), this does work

=SUMPRODUCT(--(A2:A7>J2)*(A2:A7<J3)*(B2:B7=J4)*(D2: D7))+SUMPRODUCT(--(A2:A7>J2)*(A2:A7<J3)*(C2:C7=J6)*(D2: D7))-SUMPRODUCT(--(B2:B7=G4)*(C2:C7=G6)*(D2: D7))

this adds the values when both cases are individully true and deletes the sum when they are both true
(Spaces added to stop smilies)
Hope this Helps
David

Thanks DavidCoutts,

It worked...Good Stuff!!!

In your reply you said...."although there will be a better way". If it does not take too much time can you briefly describe the better way?

Also, can you breakdown the "sumproduct(--" part of your code in layman terms?

Thanks again for your time...

I only said there theres probably a better way i just through the answer quickly. It is a bit of a contrived function i provided manipulating the data, to provide an answer rather than providing an answer for the, ill have a think and see if i can come up with a better way. or someone else might.

sumproduct basically multiplies arrays together,
(--(A2:A7>J2) basically says get an array of boolean(TRUE OR FALSE) providing on the situation, the -- basically turns this array from True to 1 and False to 0 so we basicall build up a set of equations, and it multiplies accross the rows and adds these totals of each row together, Hence the SUM of the PRODUCTS

Hope this makes sense

David

After a quick Google search I came up with a link that talks about the "--" and other related stuff.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Thanks again David...