Conditional sum - Two dimensional (advanced)

S

StephenT

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 weeks>>1 2 3
2 Type 1 10 15 11
3 Type 2 20 5 10
4 Type 1 5 12 21
Etc


So, I’d 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
 
M

Mike H

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:D1>1 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:D1>1)*(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.
 
S

StephenT

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
 
M

Mike H

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top