Overlapping Arrays?
 

Hello All:

My setup is the following:

A cell range of A1 - N1 divided into A1-C1, D1-F1, G1-I1, and J1-L1.
The first cell of each segment (A1,D1,G1,J1) will display one of four
different alpha codes (A,S,C,R) from a data list.
The next two cells of each segment will be a numeric display relating to
each specific alpha code (ex.: A1 = "C", B1 = 1.25, C1 = 1.50).

The problem is that further across ROW 1 I want to add the totals of each
aplha code in defined cells. That is, the numeric total of all "A"s in cell
P1, "S"s in Q1, "C"s in R1, "R"s in S1.

Is there a way to format an array that can recognize the connection between
the alpha code and numeric and then just add those alpha-specific numbers in
order to diplay the sum in the designated cells (P1,Q1,R1,S1)? I can't figure
it out without overlapping the arrays so they end up in conflict.

Thanks
--
6-West

Overlapping Arrays?
 

Try

For P1:

=SUMPRODUCT(--($A$1:$A$100="A"),($B$1:$B$100)+($C$1:$C$100))+SUM PRODUCT(--($D$1:$D$100="A"),($E$1:$E$100)+($F$1:$F$100))+SUM PRODUCT(--($G$1:$G$100="A"),($H$1:$H$100)+($I$1:$I$100))+SUM PRODUCT(--($J$1:$J$100="A"),($K$1:$K$100)+($L$1:$L$100))

Copy to other cells and change "A" to "C" etc

Change ranges as needed

"sixwest" wrote:

Hello All:

My setup is the following:

A cell range of A1 - N1 divided into A1-C1, D1-F1, G1-I1, and J1-L1.
The first cell of each segment (A1,D1,G1,J1) will display one of four
different alpha codes (A,S,C,R) from a data list.
The next two cells of each segment will be a numeric display relating to
each specific alpha code (ex.: A1 = "C", B1 = 1.25, C1 = 1.50).

The problem is that further across ROW 1 I want to add the totals of each
aplha code in defined cells. That is, the numeric total of all "A"s in cell
P1, "S"s in Q1, "C"s in R1, "R"s in S1.

Is there a way to format an array that can recognize the connection between
the alpha code and numeric and then just add those alpha-specific numbers in
order to diplay the sum in the designated cells (P1,Q1,R1,S1)? I can't figure
it out without overlapping the arrays so they end up in conflict.

Thanks
--
6-West

Overlapping Arrays?
 

Thanks! The arrays worked. Is there any way to extend them to other rows with
"edit,fill"?
--
6-West


"Toppers" wrote:



Try

For P1:

=SUMPRODUCT(--($A$1:$A$100="A"),($B$1:$B$100)+($C$1:$C$100))+SUM PRODUCT(--($D$1:$D$100="A"),($E$1:$E$100)+($F$1:$F$100))+SUM PRODUCT(--($G$1:$G$100="A"),($H$1:$H$100)+($I$1:$I$100))+SUM PRODUCT(--($J$1:$J$100="A"),($K$1:$K$100)+($L$1:$L$100))

Copy to other cells and change "A" to "C" etc

Change ranges as needed

"sixwest" wrote:

Hello All:

My setup is the following:

A cell range of A1 - N1 divided into A1-C1, D1-F1, G1-I1, and J1-L1.
The first cell of each segment (A1,D1,G1,J1) will display one of four
different alpha codes (A,S,C,R) from a data list.
The next two cells of each segment will be a numeric display relating to
each specific alpha code (ex.: A1 = "C", B1 = 1.25, C1 = 1.50).

The problem is that further across ROW 1 I want to add the totals of each
aplha code in defined cells. That is, the numeric total of all "A"s in cell
P1, "S"s in Q1, "C"s in R1, "R"s in S1.

Is there a way to format an array that can recognize the connection between
the alpha code and numeric and then just add those alpha-specific numbers in
order to diplay the sum in the designated cells (P1,Q1,R1,S1)? I can't figure
it out without overlapping the arrays so they end up in conflict.

Thanks
--
6-West