Sum based on column and row criteria

[Matt]

Here's what I'm trying to do with so far no success:

I have a matrix where numbers are the column headers and letters are
the row headers. I want to take a sum of the cells that meet both
column header and row header criteria. For example, the column
headers could be any number between 1 and 10 (not necessarily in any
order, there may be more than one of any number). The row headers
could be any letter between A and M (not necessarily in any order,
there could be more than 1 of any letter). I want to know the sum of
all the cells that are in a "2" column as well as a "B" row.

Or, the sum of those cells that are in a "2" column and are in rows
5:20.

Ideas?

 

[Conan Kelly]

Matt,

Column headers in B1:K1 (1-10)
Row headers in A2:A27 (A-Z)
Data in B2:K27

=SUMPRODUCT(($B$2:$K$27)*($B$1:$K$1=2)*($A$2:$A$27="B"))

the 2 & the "B" could be replace by cell references and B & 2 could be
entered into those cells.

As far as rows 5:20....hmmm....don't know......try this (not tested):

=SUMPRODUCT(($B$2:$K$27)*($B$1:$K$1=2)*(Row($A$2:$A$27)>=5)*(Row($A$2:$A$27)<=20))

HTH,

Conan

 

[T. Valko]

Try this:

=SUMPRODUCT((A2:A18="B")*(B1:K1=2),B2:K18)

Adjust ranges to suit

 

[Matt]

Excellent. Thanks!!

 

[T. Valko]

You're welcome. Thanks for the feedback!