Calculate Formulas with Visible Rows Only

[resourceguru]

Hello,

I'm designing a resourcing spreadsheet and am trying to customize the formulas so it only calculates numbers for the rows that are filtered out/visible. From somewhat similar posts, it seems like I should do something with the sumifs and function 9. I got stuck after dealing with the offset components.

For context:

• Ultimately, I want the resourcing graph to update based on which resource I've selected.
• To do that, I want to update the formulas so that they are dependent on which resource I've selected/filtered out--i.e., only the rows that are visible

Current state:

• Right now, I have formulas that only makes calculations based on certain criteria.
• E.g., SUMIFS(H:H,$B:$B,"Sold",$E:$E,"FTE")/5 is used to sum all the resource days on col H if the person is FTE and doing sold work, then divide by 5 to convert that number into headcounts for that week.

Next Steps:

• I want to customize that formula so that it only makes the calculations for resources I've selected/filtered out.
• How should I proceed with this challenge? In case it helps, I've uploaded a sample spreadsheet to get that basic formula down before applying it to the other calculations
• Spreadsheet link:

https://drive.google.com/open?id=1SijEScMNZmOOQ7cbsyJ9Uh8CnJAKQwfa

Thanks in advance!

 

[resourceguru]

To clarify: the resources in question that I want to filter is Col C ("Name"). So what I'm trying to do will be: calculate the headcoutns doing sold work for each week--for only Bob!

 

[Nishant94]

Try this with Ctrl+Shift+Enter in H7:

=SUMPRODUCT((IF(SUBTOTAL(3,OFFSET(B3,ROW($B3:$B5)-ROW($B3),,1,)),$B3:$B5)="Sold")*H3:H5)/5

Copy the formula down and across.

 

[Nishant94]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1								18-Jan	25-Jan	1-Feb	8-Feb
2	Projects	Project Status	Name	Role	Employee Status
3	Project A	Sold	Bob	Engineer	FTE			3	2	3	5
4	Project B	Sold	Ron	Designer	FTE			5	3	2	3
5	Project C	Planned	Seith	PM	FTE			1	2	3	5
6
7
8	Headcounts Doing Sold Work for Each Week							1.6	1	1	1.6
9
Sheet1

Cell Formulas
Range	Formula
H8	{=SUMPRODUCT((IF(SUBTOTAL(3,OFFSET(B3,ROW($B3:$B5)-ROW($B3),,1,)),$B3:$B5)="Sold")*H3:H5)/5}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[resourceguru]

	A	B	C	D	E	F	G	H	I	J	K
Projects	Project Status	Name	Role	Employee Status
Project A	Sold	Bob	Engineer	FTE
Project B	Sold	Ron	Designer	FTE
Project C	Planned	Seith	PM	FTE
Headcounts Doing Sold Work for Each Week

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]18-Jan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]25-Jan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]1-Feb[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]8-Feb[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1.6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1.6[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H8[/TH]
[TD="align: left"]{=SUMPRODUCT((IF(SUBTOTAL(3,OFFSET(B3,ROW($B3:$B5)-ROW($B3),,1,)),$B3:$B5)="Sold")*H3:H5)/5}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thanks! I it works with the sample data. How do I apply it so that it accounts for the Employee Status condition as well? E.g., not everyone is FTE, some will be contractor. This is why the current formula contains $E:$E,"FTE", which makes sure that I only add the data if the person is doing sold work AND is an FTE.

 

[Nishant94]

​

Book1
	A	B	C	D	E	F	G	H	I	J	K
1								18-Jan	25-Jan	1-Feb	8-Feb
2	Projects	Project Status	Name	Role	Employee Status
3	Project A	Sold	Bob	Engineer	FTE			3	2	3	5
4	Project B	Sold	Ron	Designer	FTE			5	3	2	3
5	Project C	Planned	Seith	PM	FTE			1	2	3	5
6
7
8	Headcounts Doing Sold Work for Each Week							1.6	1	1	1.6
9
Sheet1

Cell Formulas
Range	Formula
H8	{=SUMPRODUCT((IF(SUBTOTAL(3,OFFSET(B3,ROW($B3:$B5)-ROW($B3),,1,)),$B3:$B5&$E3:$E5)="SoldFTE")*H3:H5)/5}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[resourceguru]

Thanks Nishant94!

I tested it on the broader spreadsheet and it works pretty well. I did have to add a "$" to B3 so that value remains static when applying formulas to the subsequent columns.

I.e., from:
SUMPRODUCT((IF(SUBTOTAL(3,OFFSET(B3,ROW($B3:$B5)-ROW($B3),,1,)),$B3:$B5&$E3:$E5)="SoldFTE")*H3:H5)/5
to:
SUMPRODUCT((IF(SUBTOTAL(3,OFFSET($B3,ROW($B3:$B5)-ROW($B3),,1,)),$B3:$B5&$E3:$E5)="SoldFTE")*H3:H5)/5

This will take the resourcing sheet design to the next level.