Sumproduct
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
|