Summing Difficulty - Linking Data based on Condition

**figs** · 08-24-2008, 04:16 PM

tks howard,

that would make it easier... unfortunately this won't help in this case.

I should have said, I need to do this without a helper column if possible. The application I'm using this in is from two independent files, and I need to be able to run this query without manipulating either.

The only common item in both files is the ID. Is this possible?

tks.

**figs** · 08-25-2008, 10:10 AM

bump

still having trouble getting this to work... any ideas?

**NBVC** · 08-25-2008, 10:45 AM

Try this formula in C6:

=SUMPRODUCT(--(ISNUMBER(MATCH('Item Details'!$A$4:$A$12,IF(('Shop Info'!$C$9:$C$14=Summary!$A6)*('Shop Info'!$B$9:$B$14>150),'Shop Info'!$A$9:$A$14),0))),'Item Details'!$C$4:$C$12)

and this formula in D6:

=SUMPRODUCT(--(ISNUMBER(MATCH('Item Details'!$A$4:$A$12,IF(('Shop Info'!$C$9:$C$14=Summary!$A6)*('Shop Info'!$B$9:$B$14>150),'Shop Info'!$A$9:$A$14),0))),--('Item Details'!$B$4:$B$12=TRIM(SUBSTITUTE(D$5,"Quantity",""))),'Item Details'!$C$4:$C$12)

Both formula are to be individually confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the formula.

Then you can copy the formula in C6 down and the formula in D6 down and across to the right.

EDIT: Took out the +0 from each formula... not needed.