Microsoft Office Forums Sumproduct
 Register FAQ Search Today's Posts Mark Forums Read

#1
01-08-2019, 09:02 AM
 arndts Windows 10 Office 2016 Novice Join Date: Jan 2019 Posts: 3
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
Attached Images
 excel table.png (14.2 KB, 12 views)
#2
01-08-2019, 09:28 AM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,019

=SUMPRODUCT((\$B\$1:\$B\$12="Cost")*(\$F\$1:\$F\$12="High" )*(\$G\$1:\$G\$12="Full")*(\$C\$1:\$C\$12))
#3
01-08-2019, 09:31 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Moderator Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,381

@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
01-08-2019, 10:37 AM
 arndts Windows 10 Office 2016 Novice Join Date: Jan 2019 Posts: 3

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, 11 views)
#5
01-08-2019, 11:58 AM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,019

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
01-08-2019, 02:58 PM
 arndts Windows 10 Office 2016 Novice Join Date: Jan 2019 Posts: 3

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
01-09-2019, 01:10 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Moderator Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,381

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 Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post OTPM Excel 12 05-17-2017 04:04 AM Algo Excel 6 11-13-2012 07:44 AM jer Excel 9 10-14-2012 10:00 AM Portucale Excel 2 09-12-2012 10:51 AM angie.chang Excel 3 06-14-2012 10:00 AM

All times are GMT -7. The time now is 07:56 PM.

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top