Summing Difficulty - Linking Data based on Condition

[figs]

Hi,

I'm having difficulty trying to sum data based on a condition. This is normally an easy SUMPRODUCT() function, but the difficulty is created by the multiple locations of the data. The data is linked by an ID.

In the sample sheet attached, I have three worksheets, "Shop Info", "Item Details" and "Summary".

The "Shop Info" tab has a "Shop ID", "Shop Floor Area", and "Shop Location".

The "Item Details" what is sold by the shop, the colour and quantity. It has a shop ID that links the item to the Shop ID on first sheet.

In the summary, I want to sum all the quantity of the items in the shops by location, but only if the shop floor area is greater than 150. This is where I'm stuck.

The text in green should be the right answer.

All help appreciated.
Figs.

[Gearcutter]

Hi
If you allow a helper column in the Item Details sheet then you can use
sumproduct function to calculate what you require

see the attachment


Regards Howard

[figs]

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]

bump

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

[NBVC]

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.