sumproduct double criteria

[sep910]

Hi,
Before I post here. I use sumproduct to put total qty into the Defect Table.
But it take a long time for excel to run the counting process. I would like to seek any suggestion from others.

Problem:
From master data sheet, I want to plot the qty into the defect table follow by date occurring and by section

TQ

[DonkeyOte]

If you're running XL2007 exclusively (ie no backwards compatibility required) then you can dispense with SUMPRODUCT altogether and use SUMIFS which is new to XL2007

So on your Matrix sheet:

E6: =SUMIFS('Master Data'!$T$8:$T$1000,'Master Data'!$D$8:$D$1000,$C6,'Master Data'!$F$8:$F$1000,$D6,'Master Data'!$G$8:$G$1000,E$5)
applied across matrix

NOTE: you should add each Problem to each row (ie C7 should also read Problem 1 ... else you are making things more complex for yourself than necessary -- use Cond Formatting to hide the text if you prefer)

If you have backwards compatibility issues and thus can't use SUMIFS I would advise you create a concatenation key on Master Data sheet such that arrays can be avoided... eg:

Master Data!U8: =D8&":"&F8&":"&G8
copied down for all rows

Then on the Matrix sheet
E6: =SUMIF('Master Data'!$U$8:$U$1000,$C6&":"&$D6&":"&E$5,'Master Data'!$T$8:$T$1000)
applied across matrix

[sep910]

Hi,
thanks a lot,
I been writing the formula for weeks...
but you done it...

Any idea to used cond formatting to hide text..
TQ again..