Sumproduct/match problem
 

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

Sumproduct/match problem
 

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

Sumproduct/match problem
 

On Dec 2, 12:56*pm, Glenn wrote:
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- Hide quoted text -

- Show quoted text -


Glenn
I actually started that way; subsequently, I replaced the x's with the
Type*'s to provide a way to deermine if a row should be included in
the summary. I could go back easily enough, but, I couldn't come up
with a formula that accomplished what I wanted whne I had the x's; and
I still can't.
Thanks
Ken