Sumproduct/match problem

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

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
 
Ken said:
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
Click to expand...

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
 
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 -
Click to expand...


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
 
Back
Top