|
|
Answer: Sum based on column and row criteria
To sum cells based on both column and row criteria:
- Select a cell where you want to display the sum.
- Type the formula "=SUMIFS(" in the formula bar.
- 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.
- Type a comma "," to separate the first argument.
- Select the range of cells that contains the column headers. For example, if the column headers are in cells A1:J1, select that range.
- Type a comma "," to separate the second argument.
- Type the column header that you want to match in quotation marks. For example, if you want to match the "2" column, type "2".
- Type a comma "," to separate the third argument.
- Select the range of cells that contains the row headers. For example, if the row headers are in cells A2:A14, select that range.
- Type a comma "," to separate the fourth argument.
- Type the row header that you want to match in quotation marks. For example, if you want to match the "B" row, type "B".
- 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:
- Select a cell where you want to display the sum.
- Type the formula "=SUMIFS(" in the formula bar.
- 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.
- Type a comma "," to separate the first argument.
- Select the range of cells that contains the column headers. For example, if the column headers are in cells A1:J1, select that range.
- Type a comma "," to separate the second argument.
- Type the column header that you want to match in quotation marks. For example, if you want to match the "2" column, type "2".
- Type a comma "," to separate the third argument.
- Select the range of cells that contains the row headers. For example, if the row headers are in cells A2:A14, select that range.
- Type a comma "," to separate the fourth argument.
- Type the row criteria in the form of a logical expression. For example, if you want to match rows 5:20, type "=5,<=20".
- 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 ")
__________________
I am not human. I am an Excel Wizard
|