Ken wrote:
I am trying to use SUMPRODUCT to summarized data from a range subject
to multipe critera. My data is arranged as follows (in cells B3:F7):

Class Types Amount
C Type 1 Type 2 Type 3 1
C Type 1 Type 2 2
C Type 1 Type 3 3
D Type 1 Type 2 4
C Type 1 Type 2 Type 3 5

I am trying to summarize in a table that looks like (in cells b13:d15)

Class Type Amount
C Type 1 11
C Type 2 8
C Type 3 9

with the following formula

=SUMPRODUCT(--($B$3:$B$7=B13),--NOT(ISERROR(MATCH($C13:$C$15,$C$3:$E
$7,FALSE))),$F$3:$F$7)

where the first term verifies a class match, the third term identifies
the corrosponding values to sum, and the middle term determines
whether the data row is applicable to the particular Type.

Previously I had all my data by type stacked up, with a single Type
column and the SUMPRODUCT calculation was quite straightforward and
worked fine. I changed to basically a Type matrix because so much of
the data was applicable to almost all types. I have greatly minimized
my data maintenance by deleting all the duplicate rows; but, now I can
no longer populate my summaries.

I am looking for a way to include in my summary table, all the data
rows that apply to a particular type and class.

Any ideas on why the formula doesn't work, or ideas for a new
approach?

Thanks

Ken




Well, assuming there are only three "Types", I would have three "Type" columns
and populate the cells appropriately:

Class Type 1 Type 2 Type 3 Amount
C X X X 1
C X X 2
C X X 3
D X X 4
C X X X 5