In a spreadsheet (see attached imaged), I have a table that has 3 groups. Each group has 4 rows and 8 columns.

In column B, the cell in row 1 in each group contains the text "Hours". In column B, the cell in row 3 in each group contains the text "Cost". Note: the cells in column B, rows 1 and 2, are merged. Note: the cells in column B, rows 3 and 4, are merged for display purposes.

In column C, the cell in row 1 in each group contains the number of Hours. In column C, the cell in row 3 in each groups contains a currency cost value.

In column G, the cell in row 1 in each group contains the text "High", "Medium" or "Low". Note: In column G, the cells in row 1 through 4 are merged for display purposes.

In column H the cell in row 1 in each group contains the text "Full", "Mentored", "One-Time" Note: In column H, the cells in rows 1 through 4 are merged for display purpose.

Note: This is a subset of a larger table with a lot more groups with different combinations of these values.

Depending on the values in columns G and H, I want to sum the related hours and costs for each combination.

So far, I can calculate the Hours for each combination using a formula similar to the following

=SUMPRODUCT(($B$1:$B$12="Hours")*($G$1:$G$12="High ")*($H$1:$H$12="Full")*($C$1:$C$12))

But I can't figure how to construct the formula for Cost.



Thanks

Attached Images

excel table.png (14.2 KB, 18 views)

How about

=SUMPRODUCT(($B$1:$B$12="Cost")*($F$1:$F$12="High" )*($G$1:$G$12="Full")*($C$1:$C$12))

@arndts
Hi and welcome
in the future please attach a sheet instead of a picture which cannot be worked with. Thank you


(BTW you should avoid using merged cells they are nothing but trouble)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I don't think the formula you provided would work. You are only seeing one instance of the G column value "Full", whereas I may have two instances of the same value in the 3 groups.

I'm attaching the full spreadsheet to this thread to show the remaining groups as well as my efforts on doing totals.

Thanks

Attached Files

Matrix.xlsx (18.8 KB, 17 views)

I regret, but I withdraw from this thread as I simply don't want to work with merged cells.
They are about the worst thing Microsoft ever invented in Excel and should in my opinion be forbidden.

Based on a suggestion in another forum, I used the concept of "helper" cells and then hid them. Basically I used ADDRESS and OFFSET formulas together to set the values of the hidden fields based on the cells that actually contained the value in the merge fields. Now I can modify the values of the Merge fields on demand, which will change the values of the hidden fields enabling the sumproduct formulas to work as needed. Great simple solution that I didn't think about.

Using merged cells and volatile functions? Good luck

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post