Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-08-2019, 09:02 AM
arndts arndts is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2019
Posts: 3
arndts is on a distinguished road
Default 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
File Type: png excel table.png (14.2 KB, 12 views)
Reply With Quote
  #2  
Old 01-08-2019, 09:28 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,019
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

How about

=SUMPRODUCT(($B$1:$B$12="Cost")*($F$1:$F$12="High" )*($G$1:$G$12="Full")*($C$1:$C$12))
Reply With Quote
  #3  
Old 01-08-2019, 09:31 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,381
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

@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.
Reply With Quote
  #4  
Old 01-08-2019, 10:37 AM
arndts arndts is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2019
Posts: 3
arndts is on a distinguished road
Default

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
File Type: xlsx Matrix.xlsx (18.8 KB, 11 views)
Reply With Quote
  #5  
Old 01-08-2019, 11:58 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,019
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #6  
Old 01-08-2019, 02:58 PM
arndts arndts is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2019
Posts: 3
arndts is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 01-09-2019, 01:10 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,381
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with a SUMPRODUCT Formula OTPM Excel 12 05-17-2017 04:04 AM
Countifs and Sumproduct Algo Excel 6 11-13-2012 07:44 AM
sumproduct?? jer Excel 9 10-14-2012 10:00 AM
Sumproduct formula Portucale Excel 2 09-12-2012 10:51 AM
Sumproduct angie.chang Excel 3 06-14-2012 10:00 AM


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


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft