1. Registered User
Join Date
Mar 2003
Location
Knoxville, TN - USA
Posts
27

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.

2. Registered User
Join Date
Jan 2004
Location
Aberdeen, Scotland
Posts
1,067
Hi

=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
Hope this Helps
David

3. Registered User
Join Date
Mar 2003
Location
Knoxville, TN - USA
Posts
27
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?

4. Registered User
Join Date
Jan 2004
Location
Aberdeen, Scotland
Posts
1,067
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.

Originally Posted by dejavu1313
Also, can you breakdown the "sumproduct(--" part of your code in layman terms?
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

5. Registered User
Join Date
Mar 2003
Location
Knoxville, TN - USA
Posts
27

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...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•