#1




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", "OneTime" 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 
#2




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




@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)
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be) Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank. 
#4




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 
#5




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. 
#6




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.

#7




Using merged cells and volatile functions? Good luck
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be) Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank. 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Help with a SUMPRODUCT Formula  OTPM  Excel  12  05172017 04:04 AM 
Countifs and Sumproduct  Algo  Excel  6  11132012 07:44 AM 
sumproduct??  jer  Excel  9  10142012 10:00 AM 
Sumproduct formula  Portucale  Excel  2  09122012 10:51 AM 
Sumproduct  angie.chang  Excel  3  06142012 10:00 AM 