Sum based on column and row criteria
 

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?

Answer: Sum based on column and row criteria
 

To sum cells based on both column and row criteria:

1. Select a cell where you want to display the sum.
2. Type the formula "=SUMIFS(" in the formula bar.
3. Select the range of cells that you want to sum. For example, if you want to sum the cells in the "2" column and "B" row, select the entire range of cells.
4. Type a comma "," to separate the first argument.
5. Select the range of cells that contains the column headers. For example, if the column headers are in cells A1:J1, select that range.
6. Type a comma "," to separate the second argument.
7. Type the column header that you want to match in quotation marks. For example, if you want to match the "2" column, type "2".
8. Type a comma "," to separate the third argument.
9. Select the range of cells that contains the row headers. For example, if the row headers are in cells A2:A14, select that range.
10. Type a comma "," to separate the fourth argument.
11. Type the row header that you want to match in quotation marks. For example, if you want to match the "B" row, type "B".
12. Close the formula with a closing parenthesis ")" and press Enter.

The formula should look something like this: =SUMIFS(A2:J14,A1:J1,"2",A2:A14,"B")

To sum cells that are in a "2" column and are in rows 5:20:

1. Select a cell where you want to display the sum.
2. Type the formula "=SUMIFS(" in the formula bar.
3. Select the range of cells that you want to sum. For example, if you want to sum the cells in the "2" column and rows 5:20, select the range of cells.
4. Type a comma "," to separate the first argument.
5. Select the range of cells that contains the column headers. For example, if the column headers are in cells A1:J1, select that range.
6. Type a comma "," to separate the second argument.
7. Type the column header that you want to match in quotation marks. For example, if you want to match the "2" column, type "2".
8. Type a comma "," to separate the third argument.
9. Select the range of cells that contains the row headers. For example, if the row headers are in cells A2:A14, select that range.
10. Type a comma "," to separate the fourth argument.
11. Type the row criteria in the form of a logical expression. For example, if you want to match rows 5:20, type "=5,<=20".
12. Close the formula with a closing parenthesis ")" and press Enter.

The formula should look something like this: =SUMIFS(A5:A20,A1:J1,"2",A5:A20,"=5",A5:A20,"<=20 ")

Sum based on column and row criteria
 

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







"Matt" wrote in message
...
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?

Sum based on column and row criteria
 

Try this:

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

Adjust ranges to suit

--
Biff
Microsoft Excel MVP


"Matt" wrote in message
...
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?

Sum based on column and row criteria
 

Excellent. Thanks!!

Sum based on column and row criteria
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Matt" wrote in message
...
Excellent. Thanks!!