DSUM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with 4 columns.
DATE, MOLD, SOURCE, PARTS

(DATE IS A DATE, MOLD AND SOURCE ARE TEXT, AND PARTS IS NUMERIC)

I have a second table that contains.
DATE, MOLD, SOURCE2, GROSS

(DATE IS A DATE, MOLD AND SOURCE2 ARE TEXT, AND GROSS IS NUMERIC)

What I want is a function that I can place in the 5 colum of the second
table that would sum all the values in the parts column of the first table
were the DATE and MOLD match the DATE and MOLD on the corrisponding row of
the second table.

I thought I could get there with DSUM but I am having no luck.
 
Try this :

supose first table is in A8:D11 and second table is in F8:J11

in J8 put the formula :

=SUM(IF(($A$8:$A$11=F8)*($B$8:$B$11=G8);$D$8:$D$11;""))


This is a array formula you must enter it with Ctrl Shift Enter

Regards,


Carlos
 
Assuming that table 1 is in Sheet1!$A$2:$A$12 and table 2 is in
Sheet2!$A$2:$A$12.

Try:
=SUMPRODUCT(--(A2=Sheet1!$A$2:$A$12),--(B2=Sheet1!$B$2:$B$12),Sheet1!$D$2:$D$12)

Or if the row # has to be the same as the other tables row # try:
=SUMPRODUCT(--(A2=Sheet1!$A$2:$A$12),--(B2=Sheet1!$B$2:$B$12),--(ROW()=ROW(Sheet1!$B$2:$B$12)),Sheet1!$D$2:$D$12)

Copy the formula down in the 5th column of table 2.

Hope this helps.

Bill Horton
 
I have in the 5 column (H) of the second table. ('SCRAPED PARTS' is the
sheet with the first table.

=SUMPRODUCT(--('SCRAPED PARTS'!$E$1:$E$500=D2),--('SCRAPED
PARTS'!$F$1:$F$500=E2),('SCRAPED PARTS'!$H$1:$H$500))

Which does seem to work EXCEPT that I have ~10,000 rows of data when I
increse the ranges to ~5000 I only get an #N/A error.

Any ideas???
 
Hmmmm???

The formula should work. With SUMPRODUCT all the ranges have to be equal.
Example if you use E1:E500 for the first portion you must use F1:F500 for the
2nd protion. Also with SUMPRODUCT you can't include the entire column as a
range. Example E1:E65536 or E:E. The formula you list in your reply should
work. When I copy it down to over 10,000 rows of data it still works.

I don't think I understand your reply completely. Are you saying the
formula works, but if it is more than 5000 or 10000 rows it doesn't.?.?
 
Yes the change from 500 to 5000 gives me an error (in reality I will need to
include ~10,000 rows.

when I use

=SUMPRODUCT(--('SCRAPED PARTS'!$E$1:$E$500=D2),--('SCRAPED
PARTS'!$F$1:$F$500=E2),('SCRAPED PARTS'!$H$1:$H$500))

It works when I use

=SUMPRODUCT(--('SCRAPED PARTS'!$E$1:$E$5000=D2),--('SCRAPED
PARTS'!$F$1:$F$5000=E2),('SCRAPED PARTS'!$H$1:$H$5000))

I get the error.
 
You may have one or more error in your data which caused your formula not
working.

Trouble shooting, increase your range let's say from 500 to 1000 if it works
then increase it to 2000 and so on...
 
I found it. One cell in column 2 of the first table had the value #N/A.
When the range included this cell the error was propagated.
 
Back
Top