Conditional sum - Two dimensional (advanced)
 

Hi

Can you help?

I would like to sum a matrix according to a condition in a row and in
different condition in a column. I know how to do this one dimensionally
(either in the row or the column) using sumproduct, but what about two
dimensions?

Let me give an example, number of widgets produced each week by each type of
machine

A B C D
1 weeks1 2 3
2 Type 1 10 15 11
3 Type 2 20 5 10
4 Type 1 5 12 21
Etc


So, Id like the formula to be able to Sum the number of widgets produced by
machine type 1 after week1 (ie. Weeks 1)

The formula should give the answer 15+11+12+21 = 59

Thank you


p.s. No macros please

Conditional sum - Two dimensional (advanced)
 

Hi,

I reproduced your table in A1 to D4

and then the formula. Note carefull each of the ranges when setting this up
for your table

B1:D11 Is the header row excluding A1
A2:A4="Type 1" is the header column excluding A1
B2:D4 Is the data range excluding the headers

=SUMPRODUCT((B1:D11)*(A2:A4="Type 1")*B2:D4)


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"StephenT" wrote:

Hi

Can you help?

I would like to sum a matrix according to a condition in a row and in
different condition in a column. I know how to do this one dimensionally
(either in the row or the column) using sumproduct, but what about two
dimensions?

Let me give an example, number of widgets produced each week by each type of
machine

A B C D
1 weeks1 2 3
2 Type 1 10 15 11
3 Type 2 20 5 10
4 Type 1 5 12 21
Etc


So, Id like the formula to be able to Sum the number of widgets produced by
machine type 1 after week1 (ie. Weeks 1)

The formula should give the answer 15+11+12+21 = 59

Thank you


p.s. No macros please

Conditional sum - Two dimensional (advanced)
 

Works a treat. Wow, Sumproduct works across a matrix.

Is it wrong to love a formula? If so, I don't care, I ™¥ SUMPRODUCT

Thanks Mike

"Mike H" wrote:

Hi,

I reproduced your table in A1 to D4

and then the formula. Note carefull each of the ranges when setting this up
for your table

B1:D11 Is the header row excluding A1
A2:A4="Type 1" is the header column excluding A1
B2:D4 Is the data range excluding the headers

=SUMPRODUCT((B1:D11)*(A2:A4="Type 1")*B2:D4)


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"StephenT" wrote:

Hi

Can you help?

I would like to sum a matrix according to a condition in a row and in
different condition in a column. I know how to do this one dimensionally
(either in the row or the column) using sumproduct, but what about two
dimensions?

Let me give an example, number of widgets produced each week by each type of
machine

A B C D
1 weeks1 2 3
2 Type 1 10 15 11
3 Type 2 20 5 10
4 Type 1 5 12 21
Etc


So, Id like the formula to be able to Sum the number of widgets produced by
machine type 1 after week1 (ie. Weeks 1)

The formula should give the answer 15+11+12+21 = 59

Thank you


p.s. No macros please

Conditional sum - Two dimensional (advanced)
 

Gald I could help,

I'm inviting contrary opinion but I believe Sumproduct to be the most
powerful and versaltile formula in Excel
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"StephenT" wrote:

Works a treat. Wow, Sumproduct works across a matrix.

Is it wrong to love a formula? If so, I don't care, I ™¥ SUMPRODUCT

Thanks Mike

"Mike H" wrote:

Hi,

I reproduced your table in A1 to D4

and then the formula. Note carefull each of the ranges when setting this up
for your table

B1:D11 Is the header row excluding A1
A2:A4="Type 1" is the header column excluding A1
B2:D4 Is the data range excluding the headers

=SUMPRODUCT((B1:D11)*(A2:A4="Type 1")*B2:D4)


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"StephenT" wrote:

Hi

Can you help?

I would like to sum a matrix according to a condition in a row and in
different condition in a column. I know how to do this one dimensionally
(either in the row or the column) using sumproduct, but what about two
dimensions?

Let me give an example, number of widgets produced each week by each type of
machine

A B C D
1 weeks1 2 3
2 Type 1 10 15 11
3 Type 2 20 5 10
4 Type 1 5 12 21
Etc


So, Id like the formula to be able to Sum the number of widgets produced by
machine type 1 after week1 (ie. Weeks 1)

The formula should give the answer 15+11+12+21 = 59

Thank you


p.s. No macros please