Sum, Offset, Match?

c_m

Well-known Member
Joined
May 29, 2008
Messages
836
Hello Board,

I have 2 questions for the sample data posted below:

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 69px;"> <col style="width: 97px;"> <col style="width: 56px;"> <col style="width: 56px;"> <col style="width: 56px;"> <col style="width: 89px;"> <col style="width: 67px;"> <col style="width: 64px;"> <col style="width: 12px;"> <col style="width: 79px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td> <td>M</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; text-align: center;">Engr 1</td> <td style="font-weight: bold; text-align: center;">Engr 2</td> <td style="font-weight: bold; text-align: center;">Engr 3</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="font-weight: bold;">Rate:</td> <td>
</td> <td>
</td> <td style="text-align: right;">$140.50 </td> <td style="text-align: right;">$120.00 </td> <td style="text-align: right;">$100.00 </td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="font-weight: bold;">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; text-align: center;" colspan="3">Desired Result</td> <td>
</td> <td style="font-weight: bold; text-align: center;" colspan="3">Try 1</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="font-weight: bold; text-align: center;">REF NO.</td> <td style="font-weight: bold; text-align: center;">ITEM NO.</td> <td style="font-weight: bold;">DESCRIPTION</td> <td style="font-weight: bold; text-align: center;">Engr 1</td> <td style="font-weight: bold; text-align: center;">Engr 2</td> <td style="font-weight: bold; text-align: center;">Engr 3</td> <td style="font-weight: bold; text-align: center;">Sub-Total &</td> <td style="font-weight: bold; text-align: center;">% Effort</td> <td style="font-weight: bold; text-align: center;">% Effort</td> <td>
</td> <td style="font-weight: bold; text-align: center;">Col G</td> <td style="font-weight: bold; text-align: center;">Col H</td> <td style="font-weight: bold; text-align: center;">Col I</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold; text-align: center;">(Hrs)</td> <td style="font-weight: bold; text-align: center;">(Hrs)</td> <td style="font-weight: bold; text-align: center;">(Hrs)</td> <td style="font-weight: bold; text-align: center;">Task $</td> <td style="font-weight: bold; text-align: center;">(by item)</td> <td style="font-weight: bold; text-align: center;">(by ref)</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="font-weight: bold; text-align: center;">1</td> <td style="font-weight: bold;">
</td> <td>asdfwreqwet</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">$5,387.50 </td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">28.57%</td> <td>
</td> <td style="text-align: right;">$5,387.50 </td> <td>
</td> <td style="text-align: right;">28.57%</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">a</td> <td style="text-align: left;">abc</td> <td style="text-align: center;">7</td> <td style="text-align: center;">8</td> <td style="text-align: center;">9</td> <td style="text-align: right;">$2,843.50 </td> <td style="text-align: right;">52.78%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$2,843.50 </td> <td style="text-align: right;">52.78%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">b</td> <td style="text-align: left;">xyz</td> <td style="text-align: center;">8</td> <td style="text-align: center;">6</td> <td style="text-align: center;">7</td> <td style="text-align: right;">$2,544.00 </td> <td style="text-align: right;">47.22%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$2,544.00 </td> <td style="text-align: right;">47.22%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="font-weight: bold; text-align: center;">2</td> <td>
</td> <td>qwetrywertq</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">$3,165.00 </td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">16.78%</td> <td>
</td> <td style="text-align: right;">$3,165.00 </td> <td>
</td> <td style="text-align: right;">16.78%</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">a</td> <td style="text-align: left;">asd</td> <td style="text-align: center;">10</td> <td style="text-align: center;">8</td> <td style="text-align: center;">8</td> <td style="text-align: right;">$3,165.00 </td> <td style="text-align: right;">100.00%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$3,165.00 </td> <td style="text-align: right;">100.00%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="font-weight: bold; text-align: center;">3</td> <td>
</td> <td>ywertwrywry</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">$5,002.50 </td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">26.53%</td> <td>
</td> <td style="text-align: right;">$5,002.50 </td> <td>
</td> <td style="text-align: right;">26.53%</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">a</td> <td style="text-align: left;">qwr</td> <td style="text-align: center;">1</td> <td style="text-align: center;">3</td> <td style="text-align: center;">10</td> <td style="text-align: right;">$1,500.50 </td> <td style="text-align: right;">30.00%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$1,500.50 </td> <td style="text-align: right;">30.00%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">b</td> <td style="text-align: left;">treq</td> <td style="text-align: center;">2</td> <td style="text-align: center;">7</td> <td style="text-align: center;">10</td> <td style="text-align: right;">$2,121.00 </td> <td style="text-align: right;">42.40%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$2,121.00 </td> <td style="text-align: right;">42.40%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">c</td> <td style="text-align: left;">grtw</td> <td style="text-align: center;">2</td> <td style="text-align: center;">5</td> <td style="text-align: center;">5</td> <td style="text-align: right;">$1,381.00 </td> <td style="text-align: right;">27.61%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$1,381.00 </td> <td style="text-align: right;">27.61%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td style="font-weight: bold; text-align: center;">4</td> <td>
</td> <td>weryweryweyr</td> <td>
</td> <td>
</td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">$5,302.50 </td> <td>
</td> <td style="font-weight: bold; color: rgb(0, 0, 255); text-align: right;">28.12%</td> <td>
</td> <td style="text-align: right;">$5,302.50 </td> <td>
</td> <td style="text-align: right;">28.12%</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">a</td> <td style="text-align: left;">bndt</td> <td style="text-align: center;">1</td> <td style="text-align: center;">9</td> <td style="text-align: center;">8</td> <td style="text-align: right;">$2,020.50 </td> <td style="text-align: right;">38.10%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$2,020.50 </td> <td style="text-align: right;">38.10%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">b</td> <td style="text-align: left;">agytr</td> <td style="text-align: center;">2</td> <td style="text-align: center;">6</td> <td style="text-align: center;">5</td> <td style="text-align: right;">$1,501.00 </td> <td style="text-align: right;">28.31%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$1,501.00 </td> <td style="text-align: right;">28.31%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td> <td style="font-weight: bold;">
</td> <td style="text-align: center;">c</td> <td style="text-align: left;">httyu</td> <td style="text-align: center;">2</td> <td style="text-align: center;">5</td> <td style="text-align: center;">9</td> <td style="text-align: right;">$1,781.00 </td> <td style="text-align: right;">33.59%</td> <td style="font-weight: bold;">
</td> <td>
</td> <td style="text-align: right;">$1,781.00 </td> <td style="text-align: right;">33.59%</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td> <td style="font-weight: bold; text-align: center;">5</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">22</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">23</td> <td style="font-weight: bold;">TOTAL</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold; text-align: right;">$18,857.50 </td> <td style="font-weight: bold;">
</td> <td style="font-weight: bold; text-align: right;">100.00%</td> <td>
</td> <td style="text-align: right;">$18,857.50 </td> <td>
</td> <td style="text-align: right;">100.00%</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>K7</td> <td>=IF(A7<>"",SUM(OFFSET(K7,1,,MATCH(A7+1,$A$7:$A$20,0)-MATCH(A7,$A$7:$A$20,0)-1,)),SUMPRODUCT(D7:F7,$D$2:$F$2))</td></tr> <tr> <td>L7</td> <td>=IF(B7="","",K7/LOOKUP(100000000000,$A$7:A7,$K$7:K7))</td></tr> <tr> <td>M7</td> <td>=IF(B7="",K7/$K$23,"")</td></tr></tbody></table></td></tr></tbody></table>
(These formula are then copied down)
and
K23 =SUM(K7:K22)/2

Columns A through F is input, Columns G though I is the desired result and Columns K through M is the formula I tried.

The idea is to estimate hours and cost for a project. As you can see, there are bunch of tasks and sub tasks for each tasks.

Question 1:
I would like to find cost for each task by summing the estimated cost for each sub-tasks. Further, I also want to find the % effort of each task for the project and % effort of each sub-task for a task. The formula shown above works fine but I get bunch of N/A errors if I do not have 5 in cell A20. I understand the reason: When the match function tries to match 4+1=5 in column A, it returns N/A because 5 does not exist in column A. What I would like is a formula in column K that can take care of this issue. I think I also need new formula for column L too.

Question 2:
I want to compute total cost for each task without computing cost of each sub-task. I want something like =SUMPRODUCT(D8:F9,$D$2:$F$2) in cell K7 (which will return value error becacuse of different array sizes). How can I do this?

Any help is appreciated.

Thanks
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
SydneyGeek,
Thank you. I am aware that pivot table can do this but pivot table is my last preference. I would like formula approach.
 
Upvote 0
Change K7 to...
=IF(ISBLANK(A7),SUMPRODUCT(D7:F7,$D$2:$F$2),IF(ISBLANK(B8),0,SUM(OFFSET(K7,1,,MATCH(A7+1,$A$7:$A$20,0)-MATCH(A7,$A$7:$A$20,0)-1,))))

Change G23 to...
=SUMPRODUCT((A7:A22>0)*G7:G22)

There's almost certainly a more elegant way to build the K7 formula, but it works.

Denis
 
Upvote 0
SydneyGeek,
Thank you again. The formula in K7 did not work. I got N/A in K16.
Remember that 5 in A20 does not exist. I only showed it in the sample sheet for the board members to see what I am doing. MATCH(A7+1,$A$7:$A$20,0) part of the formula is creating problem.
 
Upvote 0
I chucked in a helper column (new column B).

B7 is =IF(ISBLANK(A7),B6,A7)
Fill down

Now L7 (the old K7) is
=IF(ISBLANK(A7),SUMPRODUCT(E7:G7,$E$2:$G$2),SUMIF(B8:$B$22,A7,H8:$H$22))
Fill down

Denis
 
Upvote 0
Thanks. This one works. I just had to change your suggested formula of L7 to =IF(ISBLANK(A7),SUMPRODUCT(E7:G7,$E$2:$G$2),SUMIF(B8:$B$20,A7,L8:$L$20))

Now the reason I am having second thoughts about adapting this solution is: I have macros pointing to the address of cells (the address before insertion of new column B). Inserting column B will mess up the macros. Is there any other way besides the helper column?

If there is no other way, I can use column Z or something as a helper column and live with this.
 
Last edited:
Upvote 0
No reason why not. I just used column B because it was part of the block.
Set up the helper calculation in column AA, re-point the SUMIF to refer to that column, then delete column B.

Denis
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top